使用excel vba过滤出多个条件 [英] filter out multiple criteria using excel vba

问题描述

我在 A、1、2、3、4、5 和 A、B、C 列中有 8 个变量.
我的目的是过滤掉A、B、C，只显示1-5个.

I have 8 variables in column A, 1,2,3,4,5 and A, B, C.
My aim is to filter out A, B, C and display only 1-5.

我可以使用以下代码执行此操作:

I can do this using the following code:

My_Range.AutoFilter Field:=1, Criteria1:=Array("1", "2", "3","4","5"), _
    Operator:=xlFilterValues

但是代码所做的是过滤变量 1 到 5 并显示它们.

But what the code does is it filters variables 1 to 5 and displays them.

我想做相反的事情，但得到相同的结果，过滤掉 A、B、C 并显示变量 1 到 5

I want to do the opposite, but yielding the same result, by filtering out A, B, C and showing variables 1 to 5

我试过这个代码:

My_Range.AutoFilter Field:=1, Criteria1:=Array("<>A", "<>B", "<>C"), _
    Operator:=xlFilterValues

但是没有用.

为什么我不能使用这个代码?

它给出了这个错误:

运行时错误 1004 范围类的自动过滤方法失败

Run time error 1004 autofilter method of range class failed

我该如何执行此操作?

推荐答案

我认为(从实验来看 - MSDN 在这里没有帮助)没有直接的方法可以做到这一点.将 Criteria1 设置为 Array 等效于使用下拉列表中的勾选框 - 正如您所说，它只会根据与数组中的一个匹配的项目过滤列表.

I think (from experimenting - MSDN is unhelpful here) that there is no direct way of doing this. Setting Criteria1 to an Array is equivalent to using the tick boxes in the dropdown - as you say it will only filter a list based on items that match one of those in the array.

有趣的是，如果您在列表中有文字值 "<>A" 和 "<>B" 并在这些宏记录器上进行过滤提出

Interestingly, if you have the literal values "<>A" and "<>B" in the list and filter on these the macro recorder comes up with

Range.AutoFilter Field:=1, Criteria1:="=<>A", Operator:=xlOr, Criteria2:="=<>B"

哪个有效.但是，如果您还有文字值 "<>C" 并且您在录制宏时过滤所有三个(使用勾选框)，则宏录制器会精确复制您的代码，然后失败有错误.我想我会称其为错误 - 您可以使用 UI 进行过滤，而使用 VBA 则无法进行.

which works. But if you then have the literal value "<>C" as well and you filter for all three (using tick boxes) while recording a macro, the macro recorder replicates precisely your code which then fails with an error. I guess I'd call that a bug - there are filters you can do using the UI which you can't do with VBA.

无论如何，回到你的问题.可以过滤不等于某些条件的值，但最多只能过滤两个不适合您的值:

Anyway, back to your problem. It is possible to filter values not equal to some criteria, but only up to two values which doesn't work for you:

Range("$A$1:$A$9").AutoFilter Field:=1, Criteria1:="<>A", Criteria2:="<>B", Operator:=xlAnd

根据具体的问题，有几种可能的解决方法:

There are a couple of workarounds possible depending on the exact problem:

1. 在 B 列中使用带有公式的辅助列"，然后对其进行过滤 - 例如=ISNUMBER(A2) 或 =NOT(A2="A", A2="B", A2="C") 然后过滤 TRUE
2. 如果您无法添加列，请使用带有 Criteria1:=">-65535"(或低于您预期的合适数字)的自动过滤器，这将过滤掉非数字值- 假设这是你想要的
3. 编写一个 VBA 子程序来隐藏行(与自动过滤器不完全相同，但根据您的需要它可能就足够了).

1. Use a "helper column" with a formula in column B and then filter on that - e.g. =ISNUMBER(A2) or =NOT(A2="A", A2="B", A2="C") then filter on TRUE
2. If you can't add a column, use autofilter with Criteria1:=">-65535" (or a suitable number lower than any you expect) which will filter out non-numeric values - assuming this is what you want
3. Write a VBA sub to hide rows (not exactly the same as an autofilter but it may suffice depending on your needs).

例如:

Public Sub hideABCRows(rangeToFilter As Range)
  Dim oCurrentCell As Range
  On Error GoTo errHandler

  Application.ScreenUpdating = False
  For Each oCurrentCell In rangeToFilter.Cells
    If oCurrentCell.Value = "A" Or oCurrentCell.Value = "B" Or oCurrentCell.Value = "C" Then
      oCurrentCell.EntireRow.Hidden = True
    End If
  Next oCurrentCell

  Application.ScreenUpdating = True
  Exit Sub

errHandler:
    Application.ScreenUpdating = True
End Sub

这篇关于使用excel vba过滤出多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！