Autofilter for more than 2 criteria in one column using VBA

[JohnnyBGood]

I am trying to use VBA to filter a column for more than 2 criteria.

I am using Excel 2003 currently.

I know you can work around the 3 condition max rule in conditional formatting with VBA code, but can you do similar with Autofilter?

e.g.

This works for 2 criteria:

Selection.AutoFilter Field:=9, Criteria1:="<>*SHOP*", Operator:=xlAnd, Criteria2:="<>*STOCK*"

I tried to add another:

Selection.AutoFilter Field:=9, Criteria1:="<>*SHOP*", Operator:=xlAnd, Criteria2:="<>*STOCK*", Criteria3:="<>*TOOL*"

and I get Runtime Error: 1004, application-defined or object-defined error.

I also know that I can create a helper column with a formula and filter by that... but wondering if there is a VBA trick out there.

Thanks.

[Colin Legg]

Hi,

In XL2003 you are restricted to 2 criteria. Like you say, you could create a helper column. Alternatively, you could use the Advanced Filter (which would require a criteria range).

[JohnnyBGood]

Thanks Colin,

I managed it with Advance Filter as you suggested.