VBA Autofilter exclude multiple criteria

问题:

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

回答1:

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:

• In cell O2, put =ISNA(MATCH(C2, $P$2:$P$4,0)).
• In P2 through P4, put your filter values ZC1, ZL1, and ZF1 in separate cells.

Then run this macro:

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

Before running macro:

After AdvancedFilter macro: