VBA - Check if data in listobject is filtered

Question

I am trying to develop a custom function to check if the data in a listobject is filtered.

Public Function TestFiltered() As Boolean
Dim rngFilter As Range
Dim r As Long, f As Long
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Rows.Count
f = rngFilter.SpecialCells(xlCellTypeVisible).Count
If r > f Then TestFiltered = True
End Function

However I am getting an error "Object variable not set" in Set rngFilter = ActiveSheet.AutoFilter.Range

All of my sheets will only have one listobject, but perhaps it is safer to somehow change the function to apply the range for the first listobject found in the activesheet?

Answer 1

The idea of multiplying the columns and the rows and comparing them with filterArea.SpecialCells(xlCellTypeVisible).Count is rather interesting. This is what I managed to build on it:

Public Function TestFiltered() As Boolean

    Dim filterArea As Range
    Dim rowsCount As Long, cellsCount As Long, columnsCount As Long
    Set filterArea = ActiveSheet.ListObjects(1).Range

    rowsCount = filterArea.rows.Count
    columnsCount = filterArea.Columns.Count

    cellsCount = filterArea.SpecialCells(xlCellTypeVisible).Count

    If (rowsCount * columnsCount) > cellsCount Then
        TestFiltered = True
    End If

End Function

Answer 2

Here's another approach that tests a specific listobject. It first uses the ShowAutoFilter property of the ListObject to determine whether the AutoFilter is dislayed. If so, it then uses the FilterMode property of the AutoFilter object to determine whether it's in filter mode.

Option Explicit

Sub test()

    Dim listObj As ListObject

    Set listObj = Worksheets("Sheet2").ListObjects("Table1") 'change the sheet and table names accordingly

    If IsListobjectFiltered(listObj) Then
        MsgBox listObj.Name & " is filtered", vbInformation
    Else
        MsgBox listObj.Name & " is not filtered.", vbInformation
    End If

End Sub


Function IsListobjectFiltered(ByVal listObj As ListObject) As Boolean

    If listObj.ShowAutoFilter Then
        If listObj.AutoFilter.FilterMode Then
            IsListobjectFiltered = True
            Exit Function
        End If
    End If

    IsListobjectFiltered = False

End Function