VBA自动筛选器排除多个条件 [英] VBA Autofilter exclude multiple criteria

问题描述

我需要从一个范围中排除多个条件.以下代码不会出错，但不会过滤掉所有值(例如"ZL1"仍在范围内).我试过了Operator:= xlAnd但结果没有什么不同.使用Operator:= xlFilterValues时，出现运行时错误'1004':Range类的AutoFilter方法失败.

I need to exclude multiple criteria from a range. The code below does not error but it does not filter out all values (e.g "ZL1" is still in the range). I have tried Operator:=xlAnd but the result is no different. With Operator:=xlFilterValues I get "Run-time error '1004': AutoFilter method of Range class failed.

Sub Macro1()
    Sheets("Z").Select
    myarr = Array("<>ZC1", "<>ZL1", "<>ZF1")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$M$" & lr).AutoFilter Field:=3, Operator:=xlOr, Criteria1:=(myarr)
End Sub

推荐答案

如上述注释中的Ibo所述，您不能像这样直接使用AutoFilter来排除3个或更多的值. 此特定帖子与您的情况有关.这是执行此操作的示例方法:

As Ibo mentioned in comments above, you can't use AutoFilter directly like that to exclude 3 or more values. This particular post is relevant to your situation. Here's an example way to do it:

• 在O2单元格中，放入=ISNA(MATCH(C2, $P$2:$P$4,0)).
• 在P2到P4中，将过滤器值ZC1，ZL1和ZF1放在单独的单元格中.

然后运行此宏:

Sub Macro2()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("$A$1:$M$" & lr).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("O1:O2"), Unique:=False
End Sub

运行宏之前:

在AdvancedFilter宏之后:

After AdvancedFilter macro:

这篇关于VBA自动筛选器排除多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！