Custom Auto filter for more than 2 and/or criteria

Hi Experts,

As you are aware we cannot filter more than two and/or criteria using custom auto filter.

I have a scenario where I have to filter entire data (kept in “Data” sheet) for column C “Country” based on 6 different and/or criteria. The attached file is my working template and has a fake data in it, my real data has long sentences in each cell (In Column C) and each time I have to filter data based on texts that “contains” (part of) in that sentence.

I did some research and found out that if I create a criteria range and use advanced filter I could achieve the results. You would see that in the “Input” sheet I have created a bunch of combo box and option buttons to define a criteria range that advanced filter could use. So when you enter the texts in the combo boxes and choose and/or you would see that the criteria table on the right is prepared for advanced filter.

I have also recorded a macro to apply advanced filter however the results are not coming right – If you run Macro1 nothing gets filtered. I am not sure what is the problem. I need help with a code that could filter the table in “Data” sheet (Or in the “Filtered Data”) with the criteria set in “Input” sheet. I had used the wildcards to catch a text that contains in the cell however it doesn’t seem to work.

I apologize if this is silly question, I have very basic knowledge of VBA. I really appreciate your help and time.

P.S – How do I make the tab order more efficient? So that user do not have to click on each box to enter the details.

Best,
Bunty

Hi Humdingaling,

Thanks for your prompt response, really appreciate it!!

I apologize if I wasn't clear in my post. I just need to filter my data based on 6 criteria. Specifically - Below is the image of "Custom Auto Filter" I use this to filter my data using "Contains", "AND/OR" however it has only 2 criteria/options, I need a code so that I could filter the same way but with additional 4 criteria.

Untitled.png

I am not sure how to do that

Best,
Bunty

How about using slicers in the table.

Simillary as in other programs:
- single click for one item
- ctrl + click for more different items (OR scenario)
- shift + click for range of items

Thanks Zbor for your time, really appreciate it.
In your copy I believe the criteria set up in reverse way. For instance when I select "And" in the first criteria letter o goes beneath letter a which supposed to go in rows (next to a) in the criteria range.

My working file is attached, when you look at the code you will have an idea what I am trying to achieve. In the macro first I just copy the criteria range to another range (as there are formulas in the first table) name that range as Table4 and use it as criteria range for advanced filter.

But, the problem still resides How can I tweak the code so that I can filter data with 6 different And/Or criteria?

Thank you so much for your time with this.

Best,
Bunty