How to determine filters when FilterMode, not AutoFilterMode, is true

[jjasmith4]

I'm trying to write code to get filtering information for a worksheet. There are, of course, two possibilities:

• After auto-filtering with the drop-down arrows, WS.AutoFilterMode is True, and I can enumerate the AutoFilter.Filters collection.
• After an Advanced-Filter operation, WS.FilterMode is True, but how can I determine what filters were applied, since WS.AutoFilter is Nothing?

 

[igold]

Does this do anything for you:

Sub ShowFilters()


    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim x As Long, i As Long, lCol As Long, FirstFiltRow As Long, FirstFiltCol As Long
    Dim FiltOn As Boolean
    Dim Cri1 As String, AllFilts As String
    Dim multCri1 As Long, c As Long, FirstFilt As Long
    
    FirstFiltRow = ws.AutoFilter.Range.Row
    FirstFiltCol = ws.AutoFilter.Range.Column
    lCol = Cells(FirstFiltRow, Columns.Count).End(xlToLeft).Column
    Set ws = Worksheets("Sheet1")
    With ws.AutoFilter
        x = .Filters.Count
        For i = 1 To x
            FiltOn = .Filters.Item(i).On
            If Not FiltOn Then GoTo NF
                If .Filters.Item(i).Count > 1 Then
                    multCri1 = .Filters.Item(i).Count
                    For c = 1 To multCri1
                        Cri1 = .Filters.Item(i).Criteria1(c)
                        AllFilts = AllFilts & "Columns" & i + FirstFiltCol - 1 & "  " & Cri1 & vbNewLine
                    Next
                    GoTo NF
                End If
            Cri1 = .Filters.Item(i).Criteria1
            AllFilts = AllFilts & "Column: " & i + FirstFiltCol - 1 & "  " & Cri1 & vbNewLine
NF:
        Next
    End With
    MsgBox AllFilts
    
End Sub