Tricky AutoFilter VBA Question for the VBA Gurus

[Martin514]

Hopefully some Excel VBA guru can help me on this I need a solution for the case below that I plan on using for an application I'm developing:

Case: A sheet can have an Autofiltered Range and many Autofiltered Tables(ListObjects).

Requirement: I need VBA code which can identify these objects on a sheet and put them in a VBA Collection which I can iterate through at a later point. The Collection can be populated with a generic VBA Object Type which will support both Range and ListObjects

Problem: Its easy to identify Tables(ListObjects) on a sheet - simply iterate through the Worksheet.Tables collection and determine if the Autofilter is applied. What is difficult is to identify the location of an Autofiltered Range object on a sheet with Autofiltered Tables(ListObjects)

Any thoughts on how to solve this problem?

Martin

 

[Jaafar Tribak]

How about this ?

Function AutoFilterPLUSListObjectTablesCollection(ByVal Sh As Worksheet) As Collection
    Dim i As Long, oCol As New Collection
    
    For i = 1 To Sh.ListObjects.Count
        If Sh.ListObjects(i).ShowAutoFilter Then
            oCol.Add Sh.ListObjects(i).Range
        End If
    Next i
    If Sh.AutoFilterMode Then oCol.Add Sh.AutoFilter.Range
    Set AutoFilterPLUSListObjectTablesCollection = oCol
End Function

Sub Test()
    Dim element As Object
    For Each element In AutoFilterPLUSListObjectTablesCollection(Sheet1)
        MsgBox element.Address
    Next
End Sub

 

[Martin514]

Hi Jafar,

That's perfect. Thank you so much

Martin