In Excel VBA, how do I save / restore a user-defined filter?
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 colEnd Sub
Above code does not work in Excel 2010 as it has more possible filter types. This may be true for Excel 2007 too.
Excel 2010 (XL14) introduces a number of changes over XL 2003 (XL11)
.Operator is no longer True/False but an enumeration. There is still a FALSE (=0) value, which for some reason cannot be set using Operator:= when setting Criteria1. The old TRUE values remain as xlAnd and xlOr (1 and 2).
The selected ranges (xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent) appear to be implemented as a .Operator=FALSE type that will achieve the desired result at the time the filter was set, but with a non-zero .Operator. However you cannot use Operator:= when restoring the filter. It becomes a fixed range rather than (say) top 10.
For .Operator=xlFilterValues, .Criteria1 is an array of the selected values, and seems to be restored OK with the expected statement.
The criteria for Format filters (eg cells with green fill - new in XL 2010 over XL 2007?) apparently can't be restored using the .Criteria1 mechanisms. The operator can be restored, but the pass filter isn't restored so it filters out everything. Better to just leave it off.
Extended version of above, implemented as SaveFilters() and RestoreFilters()
I have used literal numbers rather than the enumerations (xlAnd, xlOr etc) so that the code has a fighting chance of being usable in XL 2003 which didn't have those enumerations. Some of the restoration CASE statements are repeated code; this is to simplify later extensions if someone finds a way to bypass some of the limitations above.
' Usage example:' Dim strAFilterRng As String ' Autofilter range' Dim varFilterCache() ' Autofilter cache' ' [set up code]' Set wksAF = Worksheets("Configuration")'' ' Check for autofilter, turn off if active..' SaveFilters wksAF, strAFilterRng, varFilterCache' [code with filter off]' [set up special auto-filter if required]' [code with filter on as applicable]' ' Restore original autofilter if present ..' RestoreFilters wksAF, strAFilterRng, varFilterCache'~~~~~~~~~~~~~~~~~~~~~~~~~~~' Sub: SaveFilters' Purpose: Save filter on worksheet' Returns: wks.AutoFilterMode when function entered'' Arguments:' [Name] [Type] [Description]' wks I/P Worksheet that filter may reside on' FilterRange O/P Range on which filter is applied as string; "" if no filter' FilterCache O/P Variant dynamic array in which to save filter'' Author: Based on MS Excel AutoFilter Object help file'' Modifications:' 2006/12/11 Phil Spencer: Adapted as general purpose routine' 2007/03/23 PJS: Now turns off .AutoFilterMode' 2013/03/13 PJS: Initial mods for XL14, which has more operators'' Comments:'----------------------------Function SaveFilters(wks As Worksheet, FilterRange As String, FilterCache()) As Boolean Dim ii As Long FilterRange = "" ' Alternative signal for no autofilter active SaveFilters = wks.AutoFilterMode If SaveFilters Then With wks.AutoFilter FilterRange = .Range.Address With .Filters ReDim FilterCache(1 To .Count, 1 To 3) For ii = 1 To .Count With .Item(ii) If .On Then#If False Then ' XL11 code FilterCache(ii, 1) = .Criteria1 If .Operator Then FilterCache(ii, 2) = .Operator FilterCache(ii, 3) = .Criteria2 End If#Else ' first pass XL14 Select Case .Operator Case 1, 2 'xlAnd, xlOr FilterCache(ii, 1) = .Criteria1 FilterCache(ii, 2) = .Operator FilterCache(ii, 3) = .Criteria2 Case 0, 3 To 7 ' no operator, xlTop10Items, _ xlBottom10Items, xlTop10Percent, xlBottom10Percent, xlFilterValues FilterCache(ii, 1) = .Criteria1 FilterCache(ii, 2) = .Operator Case Else ' These are not correctly restored; there's someting in Criteria1 but can't save it. FilterCache(ii, 2) = .Operator ' FilterCache(ii, 1) = .Criteria1 ' <-- Generates an error ' No error in next statement, but couldn't do restore operation ' Set FilterCache(ii, 1) = .Criteria1 End Select#End If End If End With ' .Item(ii) Next End With ' .Filters End With ' wks.AutoFilter wks.AutoFilterMode = False ' turn off filter End If ' wks.AutoFilterModeEnd Function'~~~~~~~~~~~~~~~~~~~~~~~~~~~' Sub: RestoreFilters' Purpose: Restore filter on worksheet' Arguments:' [Name] [Type] [Description]' wks I/P Worksheet that filter resides on' FilterRange I/P Range on which filter is applied' FilterCache I/P Variant dynamic array containing saved filter'' Author: Based on MS Excel AutoFilter Object help file'' Modifications:' 2006/12/11 Phil Spencer: Adapted as general purpose routine' 2013/03/13 PJS: Initial mods for XL14, which has more operators'' Comments:'----------------------------Sub RestoreFilters(wks As Worksheet, FilterRange As String, FilterCache()) Dim col As Long wks.AutoFilterMode = False ' turn off any existing auto-filter If FilterRange <> "" Then wks.Range(FilterRange).AutoFilter ' Turn on the autofilter For col = 1 To UBound(FilterCache(), 1)#If False Then ' XL11 If Not IsEmpty(FilterCache(col, 1)) Then If FilterCache(col, 2) Then wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2), _ Criteria2:=FilterCache(col, 3) Else wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) End If End If#Else If Not IsEmpty(FilterCache(col, 2)) Then Select Case FilterCache(col, 2) Case 0 ' no operator wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) ' Do NOT reload 'Operator' Case 1, 2 'xlAnd, xlOr wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2), _ Criteria2:=FilterCache(col, 3) Case 3 To 6 ' xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent#If True Then wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) ' Do NOT reload 'Operator' , it doesn't work ' wks.AutoFilter.Filters.Item(col).Operator = FilterCache(col, 2)#Else ' Trying to restore Operator as well as Criteria .. ' Including the 'Operator:=' arguement leads to error. ' Criteria1 is expressed as if for a FALSE .Operator wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2)#End If Case 7 'xlFilterValues wks.Range(FilterRange).AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2)#If False Then ' Switch on filters on cell formats' These statements restore the filter, but cannot reset the pass Criteria, so the filter hides all data.' Leave it off instead. Case Else ' (Various filters on data format) wks.Range(FilterRange).AutoFilter field:=col, _ Operator:=FilterCache(col, 2)#End If ' Switch on filters on cell formats End Select End If#End If ' XL11 / XL14 Next col End IfEnd Sub
I've seen a suggestion elsewhere to achieve the required outcome by
Set up a custom view (using some improbable name to avoid overwriting things)
Execute code with autofilter off or modified
.Show the view (restore previous layout)
.Delete the view (to remove redundant data).
Good luck folks..
People looking for saving and restoring listobject / table filters (tested in Office 2007).
I have made some changes to the very good code above of Phil Spencer. Now you only need to add a listobject to the function and then it works for saving and restoring listobject filters as well:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~' Sub: SaveListObjectFilters' Purpose: Save filter on worksheet' Returns: wks.AutoFilterMode when function entered' Source: http://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save- restore-a-user-defined-filter'' Arguments:' [Name] [Type] [Description]' wks I/P Worksheet that filter may reside on' FilterRange O/P Range on which filter is applied as string; "" if no filter' FilterCache O/P Variant dynamic array in which to save filter'' Author: Based on MS Excel AutoFilter Object help file'' Modifications:' 2006/12/11 Phil Spencer: Adapted as general purpose routine' 2007/03/23 PJS: Now turns off .AutoFilterMode' 2013/03/13 PJS: Initial mods for XL14, which has more operators' 2013/05/31 P.H.: Changed to save list-object filtersFunction SaveListObjectFilters(lo As ListObject, FilterCache()) As BooleanDim ii As LongfilterRange = "" With lo.AutoFilter filterRange = .Range.Address With .Filters ReDim FilterCache(1 To .Count, 1 To 3) For ii = 1 To .Count With .Item(ii) If .On Then#If False Then ' XL11 code FilterCache(ii, 1) = .Criteria1 If .Operator Then FilterCache(ii, 2) = .Operator FilterCache(ii, 3) = .Criteria2 End If#Else ' first pass XL14 Select Case .Operator Case 1, 2 'xlAnd, xlOr FilterCache(ii, 1) = .Criteria1 FilterCache(ii, 2) = .Operator FilterCache(ii, 3) = .Criteria2 Case 0, 3 To 7 ' no operator, xlTop10Items, _xlBottom10Items, xlTop10Percent, xlBottom10Percent, xlFilterValues FilterCache(ii, 1) = .Criteria1 FilterCache(ii, 2) = .Operator Case Else ' These are not correctly restored; there's someting in Criteria1 but can't save it. FilterCache(ii, 2) = .Operator ' FilterCache(ii, 1) = .Criteria1 ' <-- Generates an error ' No error in next statement, but couldn't do restore operation ' Set FilterCache(ii, 1) = .Criteria1 End Select#End If End If End With ' .Item(ii) Next End With ' .Filters End With ' wks.AutoFilterEnd Function'~~~~~~~~~~~~~~~~~~~~~~~~~~~' Sub: RestoreListObjectFilters' Purpose: Restore filter on listobject' Source: http://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter' Arguments:' [Name] [Type] [Description]' wks I/P Worksheet that filter resides on' FilterRange I/P Range on which filter is applied' FilterCache I/P Variant dynamic array containing saved filter'' Author: Based on MS Excel AutoFilter Object help file'' Modifications:' 2006/12/11 Phil Spencer: Adapted as general purpose routine' 2013/03/13 PJS: Initial mods for XL14, which has more operators' 2013/05/31 P.H.: Changed to restore list-object filters'' Comments:'----------------------------Sub RestoreListObjectFilters(lo As ListObject, FilterCache())Dim col As LongIf lo.Range.Address <> "" Then For col = 1 To UBound(FilterCache(), 1)#If False Then ' XL11 If Not IsEmpty(FilterCache(col, 1)) Then If FilterCache(col, 2) Then lo.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2), _ Criteria2:=FilterCache(col, 3) Else lo.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) End If End If#Else If Not IsEmpty(FilterCache(col, 2)) Then Select Case FilterCache(col, 2) Case 0 ' no operator lo.Range.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) ' Do NOT reload 'Operator' Case 1, 2 'xlAnd, xlOr lo.Range.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2), _ Criteria2:=FilterCache(col, 3) Case 3 To 6 ' xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent#If True Then lo.Range.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1) ' Do NOT reload 'Operator' , it doesn't work ' wks.AutoFilter.Filters.Item(col).Operator = FilterCache(col, 2)#Else ' Trying to restore Operator as well as Criteria .. ' Including the 'Operator:=' arguement leads to error. ' Criteria1 is expressed as if for a FALSE .Operator lo.Range.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2)#End If Case 7 'xlFilterValues lo.Range.AutoFilter field:=col, _ Criteria1:=FilterCache(col, 1), _ Operator:=FilterCache(col, 2)#If False Then ' Switch on filters on cell formats' These statements restore the filter, but cannot reset the pass Criteria, so the filter hides all data.' Leave it off instead. Case Else ' (Various filters on data format) lo.RangeAutoFilter field:=col, _ Operator:=FilterCache(col, 2)#End If ' Switch on filters on cell formats End Select End If#End If ' XL11 / XL14 Next colEnd IfEnd Sub