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

jjasmith4

New Member
Joined
Aug 22, 2018
Messages
48
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,449
Office Version
365, 2010
Platform
Windows
Does this do anything for you:

Code:
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
 

Forum statistics

Threads
1,084,889
Messages
5,380,446
Members
401,679
Latest member
saffar

Some videos you may like

This Week's Hot Topics

Top