Restore Autofilter After Macro Runs

[emilyyy]

Hello,

I have a sheet with autofilter. I need to turn off the autofilter
before a macro runs but then I need to return it to the original
condition (i.e. only original filtered rows are shown). How can I
record the autofilter criteria and the field number in order to
restore the original selected autofilter?

Thanks very much in advance for any help!

Best Regards,
Emily

 

[JLatham]

There's some pretty good information about AutoFilter here:
http://www.ozgrid.com/VBA/autofilter-vba.htm
combine that with what you can find in the VB Help on AutoFilter and you end
up building something like this:

Sub SetAndResetAutoFilter()
Dim filterState As Boolean
Dim filterRangeAddress As String
Dim fc As Long ' to work through possible filter fields
Dim filterField As Long
Dim filterCriteria1 As Variant

If ActiveSheet.FilterMode Then
'remember that .FilterMode is true
filterState = True
With ActiveSheet
For fc = 1 To .AutoFilter.Filters.Count
If .AutoFilter.Filters(fc).On Then
filterField = fc
Exit For
End If
Next
filterRangeAddress = .AutoFilter.Range.Address
filterCriteria1 = .AutoFilter.Filters(filterField).Criteria1
'now show all data; turns .FilterMode off
.ShowAllData
End With
End If

MsgBox "Do other stuff here while all data is visible"

'now we set things back the way they were
If filterState Then
ActiveSheet.Range(filterRangeAddress).AutoFilter _
Field:=filterField, _
Criteria1:=filterCriteria1
End If

End Sub