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

问题描述

我在A，1,2,3,4,5和A，B，C中有8个变量。

我的目标是过滤掉A， B，C，只显示1-5。

我可以使用以下代码：

  My_Range.AutoFilter字段：= 1，Criteria1：= Array（1，2，3，4，5），运算符：= xlFilterValues 
  

但代码的作用是过滤变量1到5并显示它们。

我不会做相反的事，但产生相同的结果，通过过滤掉A，B，C和显示变量1到5

我试过此代码：

  My_Range.AutoFilter字段：= 1，Criteria1：= Array（<> A，< ;> B， -  C），运算符：= xlFilterValues 
  

但是没有工作。

为什么我不能使用这段代码？

这个错误：

运行时错误1004范围类失败的autofilter方法

我如何执行此操作？

解决方案

认为（从实验 - MSDN在这里没有帮助），没有直接的方法这样做。将 Criteria1 设置为数组相当于在下拉列表中使用勾选框 - 就像您所说的那样，它只会过滤有意思的是，如果您有文字值<> A

/ code>和B在这些宏记录器上过滤出来

  Range.AutoFilter字段：= 1，Criteria1：== A，运算符：= xlOr，Criteria2：==& 
  

哪些工作。但是，如果您的文字值C，并且在录制宏时过滤所有三个（使用勾选框），宏记录器将复制正是你的代码，然后失败与错误。我想我会称之为一个错误 - 您可以使用无法使用VBA的UI进行过滤。

无论如何，回到您的问题。可以过滤不符合某些条件的值，但最多只能为两个不适用于您的值：

 范围（$ A $ 1：$ A $ 9）。AutoFilter字段：= 1，Criteria1：=A，Criteria2：=B，运算符：= xlAnd 
  

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

1. 在列B中使用一个公式的帮助列，然后过滤 - 例如 = ISNUMBER（A2）或 = NOT（A2 =A，A2 =B，A2 =C）然后过滤 TRUE


2. 如果无法添加列，请使用带有 Criteria1的autofilter ：=> -65535（或比任何您期望的数字低的数字），将过滤非数值 - 假设这是您想要的


3. 写一个VBA子隐藏行（与自动过滤器完全不一样，但是根据你的需要可能就足够了）。

例如：

  Public Sub hideABCRows（rangeToFilter As Range）
 Dim oCurrentCell As Range 
 On Error GoTo errHandler 
 
 Application.ScreenUpdating = False 
对于每个oCurrentCell在rangeToFilter.Cells 
如果oCurrentCell.Value =A或oCurrentCell.Value =B或oCurrentCell.Value = C然后
 oCurrentCell.EntireRow.Hidden = True 
结束如果
下一个oCurrentCell 
 
 Application.ScreenUpdating = True 
退出Sub 
 
呃rHandler：
 Application.ScreenUpdating = True 
 End Sub 
  

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

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

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

I tried this code:

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

But it did not work.

Why cant i use this code ?

It gives this error:

run time error 1004 autofilter method of range class failed

How can i perform this?

解决方案

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.

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"

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. 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).

For example:

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屋！