Advanced Filter Macro Problem in Excel

I love advanced filters, but this week they caused me some serious grief.
Advanced filters in Excel are quick and powerful, and I use lots of them.

They’re a great way to pull a list of unique items from a table, or send specific data from one sheet to another.

Advanced Filters

People sometimes have trouble with an advanced filter, usually because the headings are incorrect, or there are no headings.

The advanced filter criteria can be a bit tricky too, if you go beyond the basics.

I know about those problems, and am careful to avoid them.

A Simple Filter

This week, as part of a larger procedure, I had an advanced filter, similar to the one shown below.

Filter for Unique Products

The code was designed to pull a list of unique products from column G, and put it in column K.

In this example, all 7 products in column G are unique, so they all should have been filtered to column K.

However, after I ran the filter, there was nothing in column K, except the heading!

Leftovers Clog the Filter

I checked, and the headings were an exact match, so that wasn’t the problem.

I checked the code carefully, and everything looked okay. Similar code had run hundreds of times, without a hiccup.

To troubleshoot, I tried to run the advanced filter manually, and in the screen shot below, you can see what appeared in the Advanced Filter dialog box when it opened.

Hmmm. There, in the Criteria Range box, was the range from a previous advanced filter.

Clear Criteria Range Setting

I cleared out the Criteria Range setting, and the filter ran without problems.

As expected, all 7 products showed up in column K.

Change Advanced Filter Macro Code

To prevent the macro problem from happening again in the future, I revised the macro code.

Even though the advanced filter didn’t use a criteria range, I added one to the code, with the setting as an empty string.

Good Housekeeping Prevents Clogging

With that change, the code ran perfectly, even if a previous filter had a criteria range.

To prevent your own advanced filter headaches, add that empty criteria range to your code, if you’re not using a criteria range.

It will clear out the setting, in case a previous advanced filter used a criteria range.

I’ll certainly do that from now on!
__________________