Why does ActiveSheet.FilterMode returns False when sheet has filter?

Question

I'm using the following code in an attempt to detect a filter applied to a column in a table and then clear the filter:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

According to Microsoft documentation:

This property is true if the worksheet contains a filtered list in which there are hidden rows.

This doesn't seem to be the case since ActiveSheet.Filtermode only returns True if a cell inside the table where the filter is applied is selected.

First question: Is the documentation wrong? Documentation
Second question: Is my only option to select a cell inside the table to get the expression to return True?

PS I am using Excel 2010

Edit: Answer to Question 2, Non-select based methods to clear filters...

If ActiveSheet.ListObjects(1).Autofilter.FilterMode Then ActiveSheet.ListObjects(1).Autofilter.Showalldata

ActiveSheet refers to the currently active sheet and may differ to the one you might think is active. Add Debug.Print ActiveSheet.Name to see which sheet VBA is referring to. Anyway, you should start coding more explicitly and should use ThisWorkbook.WorkSheets("The_sheet_that_I_want").FilterMode instead. Oh, and please don't add relevant information in comment. Rather edit your original post and add the relevant tag [Excel-2010] to your post. Afterwards, you can also delete your comment. — Ralph
can't you just use ActiveSheet.ShowAllData without the if? My guess is that if the filter is not applied to the whole sheet but just part of it, it can return false. — Slai
used Debug.Print to verify that the window is active on execution, still returns False with filter applied in table. — orson
@Slai ActiveSheet.ShowAllData without the If gives: "Run-time error '1004': ShowAllData method of Worksheet class failed" when cell outside the table is selected at execution, works if cell in table is selected at execution. — orson
Is it actual Excel Table that is in the Worksheet.ListObjects ? — Slai

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-08-28T11:53:33

I can replicate both your issues on Excel 2013: both the buggy False on FilterMode and the error on ShowAllData.

In response to whether the documentation is wrong, I would say that it is missing a qualification to say that the ActiveCell should be in the ListObjects DataBodyRange. Perhaps the documentation is correct but that this is a bug that has not been addressed. Maybe you can update your question with a link to the documentation?

Re your second question - I agree that using this workaround is the most obvious solution. It seems a bit unpleasant to use Select but sometimes I guess this cannot be avoided.

This is how I did it using the Intersect function to check it the ActiveCell is currently in the area of the DataBodyRange of the ListObject:

Option Explicit

Sub Test()

    Dim rng As Range
    Dim ws As Worksheet
    Dim lst As ListObject

    'get ActiveCell reference
    Set rng = ActiveCell

    'get reference to Worksheet based on ActiveCell
    Set ws = rng.Parent

    'is there a Listobject on the ActiveCells sheet?
    If ws.ListObjects.Count > 0 Then
        Set lst = ws.ListObjects(1)
    Else
        Debug.Print "No table found"
        Exit Sub
    End If

    'is cell is in the DataBodyRange of ListObject?
    If Intersect(rng, lst.DataBodyRange) Is Nothing Then
        'set the ActiveCell to be in the DataBodyRange
        lst.DataBodyRange.Cells(1, 1).Select
    End If

    'now you can safely call ShowAllData
    If ws.FilterMode = True Then
        ws.ShowAllData
    End If

End Sub

Edit

Further to @orson's comment:

What happens if you skip the If Intersect(rng, lst.DataBodyRange) Is Nothing Then and use If lst.AutoFilter.FilterMode Then lst.AutoFilter.ShowAllData End If ?

So, you can check the FilterMode of the ListObject itself and then as long as you have a reference to the ListObject you can use his code:

If lst.AutoFilter.FilterMode Then 
    lst.AutoFilter.ShowAllData 
End If

Why does ActiveSheet.FilterMode returns False when sheet has filter?

2 Answers

Edit