1

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
LondonJustin
  • 73
  • 1
  • 1
  • 7

1 Answers1

1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Brilliant answer, thank you for several extra tips embedded into your solution. – LondonJustin Nov 08 '22 at 10:07
  • (The 'case else' line should be `rg.AutoFilter Col, "3") As a follow-up question, could I have used the Object Browser to help with this, since I was an the right track with `ActiveSheet.AutoFilter.Filters` - specifically, is there a way that I could tell that 'filters' needs the column number in brackets? I'd love to improve my skills at finding my own solution in cases like this. – LondonJustin Nov 08 '22 at 10:18
  • 1
    FYI, I didn't know this either. I went with something like `Dim fs As Filters: Set fs = ws.AutoFilter.Filters`. Now when I wrote `fs` in the next line, I tried the dot i.e. `fs.` and the only useful property was the `.Count`. So I removed the dot and used the left parentheses i.e. `fs(` and it gave me `...Index As Long...`. So I tried the count and it gave me `10` (one for each column). Then I tried a loop: `Dim f As Filter: For Each f In fs...` but that errored on the first n so I did `Set f = fs(2): Debug.Print f.Criteria1` and it gave me `=3`. The rest was easy. – VBasic2008 Nov 08 '22 at 10:57
  • Thank you very much [Hvala vam puno] for explaining these techniques so clearly. I will refer to these steps next time I have a similar problem. – LondonJustin Nov 09 '22 at 10:09