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

Solution 1

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

Solution 2

An easier alternative can be to just AutoFit all rows:

Rows.AutoFit

The issue with that is that it will un-hide and auto fit all rows on the active sheet.

---

Update from http://www.contextures.com/excelautofilterlist.html

Dim list As ListObject

For Each list ActiveSheet.ListObjects
    If list.AutoFilter.FilterMode Then
        list.AutoFilter.ShowAllData
    End If
Next

Comments

[orson]

@ Robin Mackenzie 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 ?

[Robin Mackenzie]

@orson - that works irrespective of where the cell is. The work-around I've posted only applies if you are trying to use FilterMode and ShowAllData with a Worksheet per your question which references ActiveSheet. I will update my answer with an edit referencing your comment.

[EEM]

Suggest to add: On Error Resume Next and On Error Goto 0 before and after the For Each...Next or On Error Resume Next at the beginning if this corresponds to a standalone function. This is needed because if any of the ListObjects does not have and AutoFilter an error will be triggered.

[Slai]

@EEM Thanks. I updated it to supposedly not error, but can't test it.