A Clogged Filter 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.

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 carefully 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.

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 this is 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.

I cleared out the Criteria Range setting, and the filter ran without problems. All 7 products showed up in column K.

So, 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!

__________________