问题描述
我需要从一个范围中排除多个条件.以下代码不会出错,但不会过滤掉所有值(例如"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自动筛选器排除多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!