How do I get Filter Criteria in Excel 2016 using VBA?

Question

I am working on an Excel 2016 VBA Macro that applies a filter to the headings column. Afterwards, the user applies the filter criteria. I would like to be able to in VBA retrieve the filter criteria that the user applied and save it to a string array. Is there a way to access the filter criteria?

Answer 1

I checked this question and pretty much copied the first part of the code, the only thing is you don't get the field that it is applied to which can be problematic.

Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
    With .Filters
        ReDim filtarr(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filtarr(f, 1) = .Criteria1
                    Debug.Print .Criteria1
                    If .Operator Then
                        filtarr(f, 2) = .Operator
                        filtarr(f, 3) = .Criteria2
                        Debug.Print .Operator & ", " & .Criteria2
                    End If
                End If
            End With
        Next f
    End With
End With