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.

AdvFilterCode01

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.

AdvFilterCode00

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.

AdvFilterCode03

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

AdvFilterCode05

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.

AdvFilterCode04

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!

__________________

You may also like...

5 Responses

  1. Hi Debra,

    A similar situation arises when you try to use the Find and/or Replace method in VBA, if you do not epxlicitly set all arguments of those methods, Excel (VBA) will use whatever has been used last.

  2. Lincoln says:

    Thanks for the tip Debra.

    I suspect you’ve just saved me an hour or two of hair pulling.

  3. Thanks Jan Karel, that’s a great reminder.

    Lincoln, I’m here to save the hair of humanity, wherever possible!

  4. Ron de Bruin says:

    Hi Debra

    Because of the problem of wrong results if there are duplicate headers I only use
    it now for creating a unique list from one column on this page from my site.

    http://www.rondebruin.nl/copy5.htm

  5. Thanks Ron, that’s a good point about duplicate headings. I’ve encountered that problem a few times too.

Leave a Reply to Ron de Bruin Cancel reply

Your email address will not be published. Required fields are marked *