none
autofilter more than 2 criteria on strings with wildcard RRS feed

  • 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:=xlFilterValues

    However 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:=xlFilterValues

    I 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

    Tuesday, March 12, 2013 8:25 AM

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

    Tuesday, March 12, 2013 11:30 AM
  • 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

    Wednesday, March 13, 2013 7:06 AM
  • 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

    Wednesday, March 13, 2013 1:04 PM
  • 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


    Thursday, March 14, 2013 10:02 AM