Code to remember filters that are activated RRS feed

  • Question

  • HI

    I have a sql query that updates an excel table- for it to do it correctly- I need to make sure all filters are removed.

    using the below code 

    If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    End If

    Is there a way I can get the filters that were activated remembered so that after the refresh I can reactivate them?



    Wednesday, October 11, 2017 4:52 PM

All replies

  • Firstly a correction in your example code. Both conditions must be True or the ShowAllData code will error. Therefore use "And" in the If statement like the following.

    If ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode Then
    End If

    On the issue of saving the filter, this can possibly be done by saving the filters to a separate worksheet. However, with various operators really need to know how the filters are being set. ie simple single criteria, two criteria or a list of values as when multiple criteria is selected on a single filter.

    So far, I have not been able to use VBA code to set a list of Date Filters. Not saying it is impossible but I have asked the question on forums and have so far not received a reply that works with a list of dates (Individual dates are OK but not a list of dates.)

    Easiest way to describe how your filters are set is to turn on the macro recorder and set all of the filters and then turn off the recorder and copy the recorded code to your reply here. Alternatively, if you are using VBA code to set the filters then provide a copy of that.

    Regards, OssieMac

    Thursday, October 12, 2017 4:44 AM
  • Hi David_1234,

    I would suggest you use an array to iterate through the filters and items of the filter to record filter items for the sheet. This is the example.

    Dim w As Worksheet
        Dim filterArray()
        Dim currentFiltRange As String
        Dim col As Integer
        Set w = ActiveSheet
        ' Capture AutoFilter settings
        With w.AutoFilter
            currentFiltRange = .Range.Address
            With .Filters
                ReDim filterArray(1 To .Count, 1 To 3)
                For f = 1 To .Count
                    With .Item(f)
                        If .On Then
                            filterArray(f, 1) = .Criteria1
                            If .Operator Then
                                filterArray(f, 2) = .Operator
                                filterArray(f, 3) = .Criteria2 'simply delete this line to make it work in Excel 2010
                            End If
                        End If
                    End With
                Next f
            End With
        End With

    If you want to reactive the filter aftering your work, you could iterate through the array and then set filter item for each filter.

    Here is the example.

    w.AutoFilterMode = False
        For col = 1 To UBound(filterArray(), 1)
            If Not IsEmpty(filterArray(col, 1)) Then
                If filterArray(col, 2) Then
                    w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1), _
                    Operator:=filterArray(col, 2), _
                    Criteria2:=filterArray(col, 3)
                    w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1)
                End If
            End If
        Next col

    The solution is from below thread, you could take reference for more information.   

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    • Proposed as answer by Terry Xu - MSFT Wednesday, November 1, 2017 2:44 AM
    Thursday, October 12, 2017 5:57 AM