Excel macro for multiple string autofilter

Question

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?

nutsch · Accepted Answer · 2015-09-02 18:05:53Z

1

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.

answered Sep 2, 2015 at 18:05

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RonR Over a year ago

Thank you. Bummer, but now I know what I have to work with.

ManishChristian · Accepted Answer · 2015-09-02 18:20:13Z

0

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)

answered Sep 2, 2015 at 18:20

ManishChristian

3,7944 gold badges25 silver badges52 bronze badges

Collectives™ on Stack Overflow

Excel macro for multiple string autofilter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related