How can I use Excel VBA to get the value of Criteria1 in field #2 of an AutoFiltered table?-CodePudding

I would like to populate a string variable 'x' with the current value of Criteria1 for field #2 of the autofiltered range of the current sheet.

I think I'm looking for a simplified version of this but I cannot work out how to simplify it.

The filtered table will always have headers in row 10, data is in columns A:Z, and I only care about the criteria1 and criteria2 values for column B (field #2). The filter criteria will always be strings (not arrays or dates).

Sub CycleFilterSettingsFieldTWO()

Dim x As String

x= ActiveSheet.AutoFilter.field(2).criteria1 'best guess, doesn't work!
 'I've also tried ...
     'activesheet.autofilter.filters.filterarray(10,2)
     'activesheet.autofilter.filters.item(1,1)

Select Case x
    Case Is = "3"
        ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="4"

    Case Is = "4"
        ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="*" 'Show everything

    Case Else
        ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="3"

End Select

End Sub

CodePudding user response：

Cycle AutoFilter Filters

Short

x = ActiveSheet.AutoFilter.Filters(2).Criteria1

Example

Sub CycleFilterSettingsFieldTWO()

    Const Col As Long = 2

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    If ws.AutoFilterMode Then
        Dim rg As Range: Set rg = ws.AutoFilter.Range
        If ws.FilterMode Then
            Select Case ws.AutoFilter.Filters(Col).Criteria1
                Case "=3"
                    rg.AutoFilter Col, "4"
                Case "=4"
                    ws.ShowAllData
                Case Else
                    ws.ShowAllData
            End Select
        Else
            rg.AutoFilter Col, "3"
        End If
    End If
    
End Sub

Here's an example of how to loop over the filters. It is assumed that AutoFilterMode is True.

Sub LoopFiltersExample()
    
    Dim fs As Filters: Set fs = Sheet1.AutoFilter.Filters
    
    Dim f As Filter
    Dim n As Long
    
    For Each f In fs
        n = n   1
        If f.On Then
            Debug.Print n, f.Criteria1
        Else
            Debug.Print n, "Is off"
        End If
    Next f
    
End Sub