CODEWITHSUNDEEP
excelvba
Lluser
Lluser

Reputation: 256

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

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:

Watch screenshot. Shows as Criteria2 value

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.

Upvotes: 0

Views: 1171

Answers (1)

Lluser
Lluser

Reputation: 256

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.

Upvotes: 1

Related Questions