Test if the autofilter found the criteria values ?

[ImranBhatti]

Hi

Column A contains
Apple
Mango
Grapes
repeatedly

I set the criteria for autofilter as "Orange"
obviously Orange is not found in the column and the range is not filtered,but how can I test if it did not find "Orange" and the column is not not filtered.

Reason I why I need this:

I am applying autofilter on two column on my data set but if the first column is not filtered (did not found the criteria) I don't want to apply filter on other column

Hope this makes sense and doesn't need a sample book

Help would be greatly appreciated
Best Regards

Imran Bhatti

[karedog]

You can check whether the item (e.g. Orange) is exists before applying autofilter, if it is exists then do autofilter, if not exists then don't autofilter.

Please Login or Register  to view this content.

[ImranBhatti]

Correct.But what if the criteria is an array?

[sktneer]

Considering your description, easiest approach would be, filter the first column and then check the filtered cells count for that column including the header row and if the cells count is greater than 1, that means it found the criteria in that column and if the cells count is 1, it means criteria was not found and you can remove the filter from that column and skip the autofiltering the second column.

Like this...

Please Login or Register  to view this content.

[ImranBhatti]

@sktneer (Guru j)
Solutions by Karedog and you are good for single word criteria. The only thing left is what I have made red in your code.

Please Login or Register  to view this content.

[sktneer]

@ Imran
The type of criteria here doesn't make any difference. The point is to check the count of visible cells after applying the filter to the first column no matter what the criteria is.

[sktneer]

Or if you want to filter the second column only if all the items in criteria array are found in column A, you may try something like this...

Please Login or Register  to view this content.

[karedog]

To check if the criteria is an array :

Please Login or Register  to view this content.

[ImranBhatti]

Now the Excel Gurus gone wild.
Nice @Karodog and sktneer

Now I have both the options
1: if all items of array exist in column
2: if any of item of array exists in column then
filter other column

Much appreciated

[sktneer]

You're welcome Imran! Glad we could help.