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?

https://www.mrexcel.com/forum/excel-questions/333961-capture-autofilter-state.html (linked from mmurietta's linked post) — Tim Williams, Jul 05 '17 at 23:31

score 5 · Accepted Answer · edited Jul 05 '17 at 23:37

5

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

edited Jul 05 '17 at 23:37

YowE3K

23,852
7
26
40

answered Jul 05 '17 at 22:51

mmurrietta

191
3

(a) "the field that it is applied to" - isn't that determinable by `sht.AutoFilter.Range.Columns(f).Column` (i.e. the column in the worksheet that is `Columns(f)` of the filtered range) (b) The code will need to be tweaked a bit if the user is selecting a list of values in a field rather than just one or two values (`Criteria1` in that case will be a Variant array, which could be accessed by `filtarr(f,1)(1)`, `filtarr(f,1)(2)`, etc) – YowE3K Jul 05 '17 at 23:51
Thank you for your response. However, I am having issues executing the line With .Filters. The error is: "Run-time error '91': Object variable or With block variable not set" – Max Tither Jul 06 '17 at 14:01
@MaxTither - you first need to test whether `sht.AutoFilter` is not `Nothing` - if there's no Autofilter applied then you cannot access its `Filters` property – Tim Williams Jul 06 '17 at 18:08
In the end, I did not end up using this method to solve my problem, Instead I used a pivot table. I will take your word that it is a solution. Thank you @mmurrietta – Max Tither Jul 06 '17 at 21:28

score 1 · Answer 2 · answered Jan 20 '19 at 13:35

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

Fixed.See answer here: [link](https://stackoverflow.com/questions/54291924/finding-the-status-of-arbitrarily-applied-autofilter-in-excel-2016) — Neil Cothran, Jan 21 '19 at 14:30

score 0 · Answer 3 · answered Jul 06 '17 at 01:22

the code would to be like this. The code of field is cells(1, f).

Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
    With .Filters
        ReDim filtarr(1 To .Count, 1 To 4) ' change array
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filtarr(f, 1) = .Criteria1
                    filtarr(f, 4) = Cells(1, f) 'field
                    Debug.Print .Criteria1, Cells(1, f)
                    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

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

3 Answers3