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

Question

How do I save and then reapply the current filter using VBA?

In Excel 2007 VBA, I'm trying to

1. Save whatever filter the user has on the current worksheet
2. Clear the filter
3. "Do stuff"
4. Reapply the saved filter

Answer 1

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