none
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
        ActiveSheet.ShowAllData
    End If

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

    Thanks

    David

    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
        ActiveSheet.ShowAllData
    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)
    
                Else
    
                    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.

    https://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter   

    Best Regards,

    Terry


    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 MSDNFSF@microsoft.com.

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