Count Filtered Objects on a Sheet

[Martin514]

Hi Folks,

Excel allows users to have both autofiltered Tables and an autofiltered Range on the SAME sheet. How do I leverage VBA to count the number of autofiltered objects (both tables and a range) on a sheet. I'm developing an add-in where its important to identify multiple filtered ranges on a single sheet

Martin

 

[Jaafar Tribak]

Hi Folks,

Excel allows users to have both autofiltered Tables and an autofiltered Range on the SAME sheet. How do I leverage VBA to count the number of autofiltered objects (both tables and a range) on a sheet. I'm developing an add-in where its important to identify multiple filtered ranges on a single sheet

Martin

How about this function :

Function FilteredObjectsCount(ByVal Sh As Worksheet) As Long
    FilteredObjectsCount = Sh.ListObjects.Count - CLng(Sh.AutoFilterMode)
End Function

and then you can call it like this : (Change the sheet name as required)

Sub Test()
    MsgBox FilteredObjectsCount(Sheet1)
End Sub

 

[Jaafar Tribak]

Dismiss the previous function and use the following one which takes into account if a table is not showing an autofilter:

Function FilteredObjectsCount(ByVal Sh As Worksheet) As Long
    Dim oListObj As ListObject
    For Each oListObj In Sh.ListObjects
        FilteredObjectsCount = FilteredObjectsCount - CLng(oListObj.ShowAutoFilter)
    Next
    FilteredObjectsCount = FilteredObjectsCount - CLng(Sh.AutoFilterMode)
End Function

Sub Test()
    MsgBox FilteredObjectsCount(Sheet1)
End Sub

 

[PCL]

I have not the same understanding that Jaafar, but his code is a good idea to reuse

Option Explicit




Function FilteredObjectsCount(ByVal Sh As Worksheet) As Long
    FilteredObjectsCount = Sh.ListObjects.Count
    If (Sh.AutoFilterMode) Then FilteredObjectsCount = FilteredObjectsCount + 1
     
End Function




Sub Test()
    MsgBox FilteredObjectsCount(Sheet1)
End Sub

 

[Martin514]

Hi Folks, Thanks for all the replies.

On further thought, I realize my ultimate need is more difficult. Which is here https://www.mrexcel.com/forum/excel...ilter-vba-question-vba-gurus.html#post5117467