2

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.

Tim Wilkinson
  • 3,761
  • 11
  • 34
  • 62
  • Do you want the filtering to be performed "in place" or do you want the results displayed in a separate list ?? – Gary's Student Apr 23 '15 at 10:59
  • 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 Apr 23 '15 at 11:12

2 Answers2

7

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
  • 1
    See [Set Auto Filtering multiple wildcards](http://stackoverflow.com/questions/16602872/set-auto-filtering-multiple-wildcards/34822944#34822944) and [Can AutoFilter take both inclusive and non-inclusive wildcards from Dictionary keys?](http://stackoverflow.com/questions/16602872/set-auto-filtering-multiple-wildcards/34822944#34822944) for methods on getting around the two wildcard limit. –  Apr 27 '16 at 23:10
0

The second method doesn't work because if you are using wildcards in an array to autofilter you are limited to only 2 terms. There are workarounds and solutions on the web.

M Aye
  • 11
  • 1