CODEWITHSUNDEEP
vbaexcel
RonR
RonR

Reputation: 298

Excel macro for multiple string autofilter

I am trying to add code to a macro to autofilter based on if a specific column contains one of several strings. Here's what I did for 2 strings (R is the range):

R.AutoFilter Field:=ProductTypeCol, _
    Criteria1:=Array("*maintenance*", "*services*"), _
    Operator:=xlFilterValues

This works exactly as I expected, and shows some rows for each string. Then I added a third string and re-executed the macro:

R.AutoFilter Field:=ProductTypeCol, _
    Criteria1:=Array("*maintenance*", "*services*", "*training*"), _
    Operator:=xlFilterValues

Instead of showing some additional rows, this mysteriously results in no rows at all.

So in search of truth, I removed the asterisks from all three strings and got exactly what I expected: rows where the value was exactly one of those strings. Unfortunately, I really need those asterisks.

Am I missing something here?

Upvotes: 1

Views: 1876

Answers (2)

ManishChristian
ManishChristian

Reputation: 3784

As far as I am aware of, you cannot filter on the same column by more than two criteria with wildcard. I think what you would need to do is to move the filtering requirement into an additional column populated by a formula along the lines of:

Assuming ProductTypeCol is column A. 

=SUM(COUNTIF($A2,{"=*maintenance*","=*services*","=*training*"}))>0

Then filter on this column for the Trues (you can extend this to more search terms if required)

Upvotes: 0

nutsch
nutsch

Reputation: 5962

You cannot filter more than two criteria with wildcards. If you want to do that, you'll have to add a temporary helper column, or filter with a loop, or another workaround.

Upvotes: 1

Related Questions