Thursday, April 7, 2011

Excel AutoFilter and multiple criteria for a single column

I had a recently frustrating experience with Excel macros, AutoFilter and multiple search criteria for a single column.

For a single criteria in a single column, you may specify something along the lines of

Selection.AutoFilter Field:=1, Criteria1:="Fred"

To do two criteria, you can use an Or operator, like so

Selection.AutoFilter Field:=1, Criteria1:="Fred", Operator:=xlOr, Criteria2:="Barney"

But if you wanted to add more criteria to the search, you can not just tack on another Operator:=xlOr, Criteria3:="Wilma"

In my search for a solution, I found all sorts of wonderful kludges, involving multiple autofilters, saving range results and doing a union of those results.  Yeck.

The finally, I happened upon the xlFilterValues operator.

Selection.AutoFilter Field:=1, Criteria1:=Array("Fred","Barney","Wilma","Betty"), Operator:=xlFilterValues


No comments:

Post a Comment