Asked by:
autofilter more than 2 criteria on strings with wildcard
Question

Hi,
I am trying to autofilter more than 2 criteria, for example 3 criteria.
Below code works fine as the criteria are exact match strings
ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=apple", "=orange", "=grape"), Operator:=xlFilterValuesHowever below code does not work when the criteria are strings with wildcard "*"
ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=*apple*", "=*orange*", "=*grape*"), Operator:=xlFilterValuesI need a wildcard * indicating strings contains instead of exact match the criteria.
Data looks like this
Fruits
apple, orange, peach
orange, strawberry
banana, peach
grape, peach
After autofilter, any rows that contains one of the criteria(in this case orange, apple, grape) will remain.
Appreciate if anyone can provide a solution.
Thanks
All replies

You can use only one wildcard expression for Criteria1, and one wildcard expression for Criteria2, with xlOr as Operator.
So you can specify at most two wildcard criteria this way.
Alternatives:
 A helper column with formulas such as =SUM(COUNTIF($G2,{"*apple*","*orange*","*grape*""})); filter on the column with the formulas being > 0.
 Advanced filter.
Regards, Hans Vogelaar

Dear Hans,
That is awesome. Thank you so much!
However, I encounter another obstacle when I tried to pass variables using your first solution.
 A helper column with formulas such as =SUM(COUNTIF($G2,{"*apple*","*orange*","*grape*""})); filter on the column with the formulas being > 0.
My code as per below:
[code]
Dim fruits As Strings
fruits = "*" & fruitsinput.Text & "*"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=SUM(COUNTIF(RC[1],{fruit}))"
[/code]
It throws application defined or object defined error at =SUM(COUNTIF(RC[1],{fruit}))
Tried all sorts of workarounds like "="&fruit but failed..
Appreciate if you can provide any recommendations.
Warmest Regards
David

1) You declare fruits, but use fruit later on.
2) The data type for text is String, not Strings.
3) You must concatenate with the value of the variable.
4) The expression {...} should be enclosed in quotes on the formula.
So:
Dim fruit As String fruit = "*" & fruitsinput.Text & "*" Range("H2").FormulaR1C1 = "=SUM(COUNTIF(RC[1],{""" & fruit & """}))"
Regards, Hans Vogelaar

Dear Hans,
Again, thank you so much! It worked like a charm.
I apologize for the typos in the first 2 points mentioned.
My bottleneck was at point 4. Never thought of using 3 quotations (") at each side.
A thousand thanks and appreciated.
Warmest Regards
David