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

Answer 2

I'd like to add a bit to the discussion. I found this (and other excellent sources of help) when investigating how to "return" the filter status. In my case, I want to DISPLAY the filter status in a cell on a worksheet.

As I said, this question and many others like it were quite useful. From that, I was able to build the function shown in the code below.

I pass it the name of the Table for which I want the filter status... thus it's passed in as a RANGE and it then needs to look in the PARENT (sheet) for information. This is because there may be several Tables on the SHEET from which it comes, so I can't just use the SHEET itself to get Autofilter information.

This works well, except for one thing: if the active cell on the worksheet is NOT within the table in question, the function will see the number of filters as zero (WholeTable.Parent.Autofilter.Filters.Count in the sample below). I do not understand why this is, nor how to prevent it. If the active cell IS within the table range, it works perfectly.

Any hints would be appreciated!

Code:

---

Public Function AutoFilterCriteria(ByVal WholeTable As Range) As String

On Error Resume Next

If WholeTable.Parent.AutoFilter Is Nothing Then                     ' if no filter is applied
    AutoFilterCriteria = "None"
    On Error GoTo 0
    Exit Function
End If

Dim LongStr As String, FirstOne As Boolean
LongStr = ""
FirstOne = False

Dim iFilt As Integer
For iFilt = 1 To WholeTable.Parent.AutoFilter.Filters.Count         ' loop through each column of the table
    Dim ThisFilt As Filter
    Set ThisFilt = WholeTable.Parent.AutoFilter.Filters(iFilt)      ' look at each filter
    On Error Resume Next
    With ThisFilt
        If .On Then
            If FirstOne Then LongStr = LongStr & " AND "            ' Get column title
            LongStr = LongStr & "[" & WholeTable.Parent.Cells(WholeTable.Row - 1, WholeTable.Column + iFilt - 1).Value & ":"
            On Error GoTo Handle
            If .Operator = xlFilterValues Then                      ' dont really care to enumerate multiples, just show "multiple"
                LongStr = LongStr & "<Multiple>]"
            ElseIf .Operator = 0 Then
                LongStr = LongStr & .Criteria1 & "]"
            ElseIf .Operator = xlAnd Then
                LongStr = LongStr & .Criteria1 & " AND " & .Criteria2 & "]"
            ElseIf .Operator = xlOr Then
                LongStr = LongStr & .Criteria1 & " OR " & .Criteria2 & "]"
            End If
            On Error GoTo 0
            FirstOne = True
        End If
    End With
Next

AutoFilterCriteria = LongStr
On Error GoTo 0
Exit Function

Handle:
AutoFilterCriteria = "! Error !"
On Error GoTo 0

End Function