AutoFilter a column on 3 criteria using Arrays does not work

[ritesh.sharma25]

Hi All,

My requirement is to filter a column based on 3 criteria, I found on web that easiest way to do it will be using Array, but is seems that when I pass the values to the Arrays in asterix *SearchTerm*, the filter returns blanks.

This is my source Data:

SourceData.PNG

When I use Macro 1 as below, it filters correctly as expected.

Macro1Works.PNG

But When I pass the values enclosed in asterix, it does not filter and gets blank.

Macro1DoesNotWorks.PNG

This is the result when the values are passed as "*Green*" and so on.

result.PNG


Could you please help.

[LJMetzger]

Hi ritesh.sharma25,

The Autofilter xlFilterValues operator does not allow wildcards to the best of my knowledge. If you have only have two 'or' items, the xlOr (or xlAnd) operator can be used with wildcards. For 3 or more 'or' items with wildcards, you have to identify all the items in the list that match your criteria. This can be done with 'Scripting Dictionary' code.

See the following link for examples: https://stackoverflow.com/questions/...22944#34822944

Lewis

[ritesh.sharma25]

Thanks Lewis.

The method you mentioned, seems a bit complicated to me as I am not that good with macro, still will give it a try

Regards,
RVS

[LJMetzger]

Thanks for the rep points.

It's not that difficult, it only looks that way. It's a two part process:

a. Create a 'Scripting Dictionary' that contains a list of all the Unique values in your 'AutoFilter' Column. A 'Scripting Dictionary' is a built in Excel item that has many uses, and is very good at creating a list of unique items. For reference see https://www.experts-exchange.com/art...ss-in-VBA.html
http://www.snb-vba.eu/VBA_Dictionary_en.html

b. Create an array of items from the Dictionary that match your search criteria.

If you need additional help upload a sample workbook, and indicate what you want to do.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.