Auto filter with multiple wildcards

Question

Is there a way to use an Excel auto filter, whereby you set a predefined list of values to filter by, and it will return all the cells in a column that contain that phrase? For example imagine the data below (all in a single column).

       A
1    NAMES
2  Brian
3  Brian, John
4  Brian, Mark
5  Mark, John

The filter would have three predefined values, Brian, John and Mark, when you select either 1 or more options, it satisfies all selected filters? So in this example Brian would return the first three cells, where as selecting Mark and John would return only the last.

I know this is possible to do once in a list of data using the Advanced Filter, but I'm looking for a real-time way of doing this many times.

Do you want the filtering to be performed "in place" or do you want the results displayed in a separate list ?? — Gary's Student
In place, i have explored showing it in a different place but unfortunately that will not work for my specific application, so has to remain in place. — Tim Wilkinson

Unknown Unknown · Accepted Answer · 2015-04-24T01:19:53

You cannot filter more than two criteria with wildcards. The nature of your sample data is such that you would be using ="Brian*", ="Mark*" or ="*John" to wildcard the first names as either Begins with... or Ends with... criteria. You can only use two of these in any one filter operation. You cannot add a third by creating an array of wildcarded values.

This works:

with activesheet.cells(1, 1).currentregion
    .AutoFilter Field:=1, Criteria1:="=Brian*", Operator:=xlOr, Criteria2:="=*John"
end with

This does not work:

with activesheet.cells(1, 1).currentregion
    .AutoFilter Field:=1, Criteria1:=Array("Brian*", "Mark*", "*John"), Operator:=xlFilterValues
end with

Auto filter with multiple wildcards

2 Answers