Contextures Blog

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.

For example, this Excel 2010 sheet has a single list that is not a named table. An AutoFilter was applied to this list, and the AutoFilter arrows have been turned off.

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!

Macro Code Count AutoFilters

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.

The worksheet 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.

Different Count Results

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:

1. Named Table – no AutoFilter – no arrows
2. Named Table – AutoFilter – visible arrows
3. 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.

And More Results

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.

I’ll stick to the AutoFilterMode for a count of worksheet AutoFilters, and use other code to count the ListObject 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.

___________