In Excel VBA, how do I save / restore a user-defined filter?

by

Have a look at Capture Autofilter state

To prevent link rot, here is the code (credit to original author):

Works with Excel 2010, just delete the commented line marked.

Sub ReDoAutoFilter()
    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

    'Remove AutoFilter
    w.AutoFilterMode = False

    ' Your code here

    ' Restore Filter settings
    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
End Sub

More Related Contents:

  1. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. Function to convert column number to letter?
  4. Read/Parse text file line by line in VBA
  5. How to use the Implements in Excel VBA
  6. VBA code doesn’t run when cell is changed by a formula
  7. What is the difference between dim and set in vba
  8. How to set recurring schedule for xlsm file using Windows Task Scheduler
  9. Change date format using substitute or replace
  10. Getting the actual usedrange
  11. Delete a row in Excel VBA
  12. VBA: Convert Text to Number
  13. Iterating through populated rows
  14. How can I run VBA code each time a cell gets its value changed by a formula?
  15. Using VBA to detect which decimal sign the computer is using
  16. Referencing Excel Userform Control Name Value From String (VBA)
  17. Loop through each row of a range in Excel
  18. Fill formula down till last row in column
  19. How to create a range from two ranges in VBA?
  20. If string contains word from list, return value adjacent to list value
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. excel vba freeze pane without select
  25. Converting a string representation of a constant into a constant?
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Create folder path if does not exist (saving from VBA)
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment