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

user_4959398 0
Viewed 12861

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?

vba excel
3 Answers

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
user_100518520
Related