Excel VBA: How to save filters applied to ListObject for re-using them later

Question

during my procedure I want to remove all filters applied to ListObject, do something else and then re-apply these filters back.

I'm struggling in the first step - save filter settings.

ListObject.AutoFIlter.Filters is a collecton object where Item is Filter object. I want to store properties of these filters and re-use, but getting Run-time error '1004'

It's because Filter property Criteria2 is usually not set and has value: <Application-defined or object-defined error>. As you can see in an image below:

Code in a module

Public Sub FilterManupulation()
    Dim Table As ListObject
    '...
    'save filters
    Dim Saved As Variant
    Saved = SaveTableFilters(Table)
    '...
    'disable filters
    call Table.AutoFilter.ShowAllData
    '...
End Sub

Function code

Public Function SaveTableFilters(ByVal Table As ListObject) As Variant
    Dim ColumnCount As Long
    ColumnCount = Table.ListColumns.Count
    
    Dim SavedFilter() As SavedFilter 'SavedFilter is custom Class
    ReDim SavedFilter(1 To ColumnCount) 'Set array size acc. to column count
    
    Dim i As Long
    For i = 1 To ColumnCount
        Dim Filter As Filter
        Set Filter = Table.AutoFilter.Filters.Item(i)
        'Save only filters which are On
        If Filter.On Then
            Set SavedFilter(i) = New SavedFilter
            With SavedFilter(i)
                .Criteria1 = Filter.Criteria1
                .Criteria2 = Filter.Criteria2 'This throws Run-time error '1004'
                .Operator = Filter.Operator
            End With
        End If
    Next i 
    SavedTableFilters = SavedFilter()   
End Function

SavedFilter Class Module code

Public Criteria1 As Variant
Public Criteria2 As Variant
Public Operator As XlAutoFilterOperator

Thanks for suggestions.

Solution

This solution is meant for Excel versions officially supported in time of writing (01/2021).

If you need compatibility with old (already unsupported) Excel versions see answer in: In Excel VBA, how do I save / restore a user-defined filter? by @PhilSpencer

Public Function SavedTableFilters(ByVal Table As ListObject) As Variant
    Dim ColumnCount As Long
    ColumnCount = Table.ListColumns.Count
    
    Dim SavedFilter() As SavedFilter
    ReDim SavedFilter(1 To ColumnCount)
    
    Dim i As Long
    For i = 1 To ColumnCount
        Dim Filter As Filter
        Set Filter = Table.AutoFilter.Filters.Item(i)
        'Save only filters which are On
        If Filter.On Then
            Set SavedFilter(i) = New SavedFilter
            With SavedFilter(i)
                .Criteria1 = Filter.Criteria1
                .Operator = Filter.Operator

                On Error Resume Next 'Error handling (if error skip to next line)
                    .Criteria2 = Filter.Criteria2
                    If Err.Number <> 0 And Err.Number <> 1004 Then
                        'Code what to do if other error than 1004 is thrown
                    End If
                On Error GoTo 0 'Re-set to default
            End With
        End If
    Next i
            
    SavedTableFilters = SavedFilter()
    
End Function

The trick is done by

• "ignoring" errors in code On Error Resume Next while accessing Filter.Criteria2.

• All errors are "ignored" after On Error Resume Next, this is why there is If Err.Number <> 0 And Err.Number <> 1004 which is true in case of other error.

• On Error GoTo 0 is return to default behavior

The first idea was to decide if to even access Filter.Criteria2 based on Filter.Operator value, but I'm not sure if it is reliable.

Comments

[Steve]

What model of laptop do you have Rodney? This will help us find the drivers for you.

[Tim Williams]

stackoverflow.com/questions/9489126/…

[Lluser]

Thank you @TimWilliams, this helps. (I already saw it before, but consider it - based on top rated answer - obsolete. My bad.