VBA Autofilter exclude multiple criteria

Question

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

Can you include a snapshot of your sample data and what you want the filtered data to look like? — takanuva15, Jul 31 '17 at 16:43
All I want is for the range to exclude any row where column C has the value "ZC1", "ZL1" or "ZF1" — James Steele, Jul 31 '17 at 16:48
@JamesSteele this is one of the cases that you cannot use VBA. You have to use a workaround. Create a helper column, where if any of these values existed in the 3rd column, the helper column should show 0 otherwise 1. Then create an autofilter on the helper column and filter values 1. Here is an extensive post: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba — Ibo, Jul 31 '17 at 17:12

score 1 · Accepted Answer · answered Jul 31 '17 at 17:35

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:

VBA Autofilter exclude multiple criteria

1 Answers1

Linked