Capture and restore autofilter criteria in Excel 2007 for xlFilterValues operator?

Hi!
For my purpose, to unhide the rows that the Autofilter made, do the stuff I want and then set the Autofilter on with it's specific filterings set again.
This works in Office 2010 with multiple selected filters!

I slightly changed the code above
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)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f) = Array() ' this to type caste to an array type!
filterArray(f) = .Criteria1
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)) Then
w.Range(currentFiltRange).AutoFilter Field:=col, Criteria1:=filterArray(col)
End If
Next col
End Sub



Br Stefan

Thanks Stefan,

I tried your code on Excel 2007 and it only retained the first element from filterArray(col) in Criteria1 for any columns that have filter "Operator" = xlFilterValues; however, your code gave me a an idea and I have found that the following code (to simulate polymorphism) works for complex filters in Excel 2007 where AutoFilter is turned on:

Two class modules are required
myFilterDetailClass
myFilterClass

CLASS: myFilterDetailClass

Dim Criteria1() As Variant
Public Criteria2 As Variant
Public Count As Integer
Public IsOn As Boolean
Public Operator As XlAutoFilterOperator

Public Sub SetCriteria1(argList As Variant)
If IsArray(argList) Then
Criteria1 = argList
Else
ReDim Criteria1(0)
Criteria1(0) = argList
End If
End Sub

Public Function GetCriteria1() As Variant
GetCriteria1 = Criteria1
End Function

CLASS: myFilterClass
Public myFilter As New myFilterDetailClass
Public myIndex As Integer

CODE SNIPPET:

Sub ReDoAutoFilterAsCollection()
'
Dim w As Worksheet
Dim filterCollection As New Collection
Dim myFilterInst As myFilterClass
Dim currentFiltRange As String
Dim col As Integer, f As Integer
Set w = ActiveSheet
' Capture AutoFilter settings
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
For f = 1 To .Count
With .Item(f)
If .On Then
Set myFilterInst = New myFilterClass
myFilterInst.myIndex = f
myFilterInst.myFilter.Count = .Count
If .Operator = xlFilterValues Then
myFilterInst.myFilter.SetCriteria1 (.Criteria1)
Else
myFilterInst.myFilter.SetCriteria1 (.Criteria1)
myFilterInst.myFilter.Criteria2 = .Criteria2
End If
myFilterInst.myFilter.IsOn = .On
myFilterInst.myFilter.Operator = .Operator

filterCollection.Add Item:=myFilterInst, Key:=CStr(f)

End If
End With
Next f
End With
End With
'Remove AutoFilter
w.AutoFilterMode = False
w.AutoFilter.ShowAllData
' Your code here
' Restore Filter settings
For Each myFilterInst In filterCollection
If myFilterInst.myFilter.Operator = xlFilterValues Then
w.Range(currentFiltRange).AutoFilter Field:=myFilterInst.myIndex, Criteria1:=myFilterInst.myFilter.GetCriteria1(), Operator:=myFilterInst.myFilter.Operator
Else
w.Range(currentFiltRange).AutoFilter Field:=myFilterInst.myIndex, Criteria1:=myFilterInst.myFilter.GetCriteria1(), Criteria2:=myFilterInst.myFilter.Criteria2, Operator:=myFilterInst.myFilter.Operator
End If
Next myFilterInst
' Cleanup here
Do While filterCollection.Count > 0
filterCollection.Remove (1)
Loop
Set myFilterInst = Nothing
Set filterCollection = Nothing
End Sub