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!
__________________
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.
Thanks for the tip Debra.
I suspect you’ve just saved me an hour or two of hair pulling.
Thanks Jan Karel, that’s a great reminder.
Lincoln, I’m here to save the hair of humanity, wherever possible!
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
Thanks Ron, that’s a good point about duplicate headings. I’ve encountered that problem a few times too.