Trouble Counting Excel AutoFilters on Sheet
It seemed simple enough, but counting the AutoFilters on an Excel sheet is a tough job! The answer to "How many worksheet AutoFilters are there?" is "It depends!"
You can read the fascinating (to me!) results below.
The Good Old Days
In the old days, before Excel 2003, you could only have one AutoFilter on a sheet. That’s pretty easy to count – either 1 or 0.
If you point to one of the headings, where the hidden arrow is, the tooltip for that filter appears, so that shows us the AutoFilter is still active.
Count the Worksheet AutoFilters
To count the worksheet AutoFilters, I usually use AutoFilterMode to check if one exists. Recently, Pascal (forum name: p45cal) emailed me, to suggest that checking for a worksheet AutoFilter would be more reliable. Thanks, Pascal, for inspiring this test!
This code tests for a worksheet AutoFilter, by using either AutoFilterMode or AutoFilter:
Sub CountSheetAutoFilters() Dim iARM As Long Dim iAR As Long 'counts all worksheet autofilters 'even if all arrows are hidden If ActiveSheet.AutoFilterMode = True Then iARM = 1 Debug.Print "AutoFilterMode: " & iARM If Not ActiveSheet.AutoFilter Is Nothing Then iAR = 1 Debug.Print "AutoFilter: " & iAR End Sub
When I test that code in the Immediate window, both counting methods show 1 AutoFilter.
Named Table on the Worksheet
What happens if there is a named table on the worksheet, and it has its own AutoFilter? I ran the code again, on the worksheet shown below. It has a named table, and it has an AutoFilter applied, with all the arrows hidden.
When I test the code in the Immediate window, both counting methods show zero AutoFilters. I consider that count correct, because there is a ListObject with an AutoFilter, but no worksheet AutoFilter.
In the screen shot above, you can see that cell A1 is selected – outside of the named table. When I selected cell B1, inside the table, and ran the code, the results were different. AutoFilterMode was still zero, but AutoFilter detected one.
Apparently, Excel is counting the active cell’s table as a worksheet AutoFilter, with the AutoFilter counting method. I’d rather go with the AutoFilterMode’s zero, and count the ListObject AutoFilters separately.
Test With Visible AutoFilter Arrows
Maybe the hidden arrows are affecting the results. To check, I ran code to show all the list AutoFilter arrows, and tested again.
The results were the same as in the previous tests, so visible arrows don’t make a difference.
Multiple Tables on Worksheet
For some final tests, I created a sheet with 3 lists:
- Named Table – no AutoFilter – no arrows
- Named Table – AutoFilter – visible arrows
- Worksheet table – AutoFilter – visible arrows
With a cell in Named Table 1 selected, AutoFilterMode counted one, and AutoFilter counted zero. As in the previous test, the AutoFilter counting method is based on the active cell’s table AutoFilter. It doesn’t detect the AutoFilter in Worksheet Table 3.
With any other cell in the worksheet selected, the results were different – both AutoFilter and AutoFilterMode counted one – the correct count of worksheet AutoFilters.
Counting Worksheet AutoFilters Conclusion
Because ActiveSheet.AutoFilter detects the AutoFilter in the active cell, it could cause a miscount of worksheet AutoFilters.
AutoFilters in Other Excel Versions
After running these tests in Excel 2010, I tested the AutoFilter counting code in Excel 2003, and got the same results.
If you find different results in other versions of Excel, please let me know.