1

im working with this code from https://stackoverflow.com/a/9490281/10995113. I want it to save the filters, do manual stuff and then reapply saved filters. Can someone help me how to edit this code to have this working? I need to somehow run the restore part but when i separate the capture and restore to other subs then restore not working.

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

Call filter_model 'after i do the manual stuff

' 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

BigBen
  • 46,229
  • 7
  • 24
  • 40
Tomz
  • 81
  • 8
  • Global variables retain their values until either they are changed or the file containing those variables is closed. I think you can define `filterArray` as global variable and `Capture AutoFilter settings` in them with one macro and then after doing the manual stuff before closing the file you can run `Restore Filter settings` macro using the `filterArray` global variable. You can then also make sure with event procedure that before closing the workbook the autofilter is restored. ... Also, value of a global variable will be nullified when a VBA error occurs. – Naresh Jun 30 '21 at 16:30
  • @Naresh tried to do this at this moment but i cant get it working, how to edit code to manage this? – Tomz Jul 01 '21 at 06:48
  • 1
    Also, refer [this link](https://learn.microsoft.com/en-us/office/vba/api/excel.autofilter) for separate macros for the two purposes. In there, in the first procedure `ChangeFilters` you can add `On On Error Resume Next` just before `filterArray(f, 3) = .Criteria2` to avoid any error caused by absence of Criteria2. ... For me, it works fine after removing `w.Range("A1").AutoFilter field:=1, Criteria1:="S" ` and changing the worksheet name – Naresh Jul 01 '21 at 08:16
  • Thats it! From the link you provided, its working like a charm, thank you! – Tomz Jul 01 '21 at 08:44

0 Answers0