Saving and restoring autofilter criteria, trouble with dates

Question

Two subs below; you can save the autofilter criteria on a table and restore them at any point.

Public Sub SaveFilter()
    Dim Value As Variant
    Dim c As Integer
    Dim Arr As Variant
    ReDim Arr(0)
    Dim t As Variant

    Set cocoll = New Collection
    Set crcoll = New Collection

    With shMain.ListObjects(1).AutoFilter
        If .FilterMode Then
            For c = 1 To .Filters.Count
                If .Filters(c).On Then
                    If IsArray(.Filters(c).Criteria1) Then
                        For Each Value In .Filters(c).Criteria1
                            Arr(UBound(Arr)) = Mid(Value, 2, Len(Value))
                            ReDim Preserve Arr(UBound(Arr) + 1)
                        Next
                        ReDim Preserve Arr(UBound(Arr) - 1)
                    Else
                        Arr(UBound(Arr)) = .Filters(c).Criteria1
                        ReDim Preserve Arr(UBound(Arr) + 1)
                        On Error Resume Next
                        Arr(UBound(Arr)) = .Filters(c).Criteria2
                        If Err <> 0 Then ReDim Preserve Arr(UBound(Arr) - 1)
                        On Error GoTo 0
                    End If

                    cocoll.Add Arr
                    crcoll.Add c
                    ReDim Arr(0)
                End If
            Next
        End If
    End With

    shMain.ListObjects(1).AutoFilter.ShowAllData
End Sub

Public Sub RestoreFilter()
    Dim i As Integer

    For i = 1 To crcoll.Count
        shMain.ListObjects(1).Range.AutoFilter Field:=crcoll.item(i)
        shMain.ListObjects(1).Range.AutoFilter Field:=crcoll.item(i), Criteria1:=cocoll.item(i), Operator:=xlFilterValues
    Next i
End Sub

Here's the problem: it throws an error as soon as I try to filter on a date column. I've tried several formats like "yyyy/mm/dd", "dd/mm/yyyy", "mm/dd/yyyy". I figured I could try the "General" NumberFormat but if it's already filtered I can't turn the NumberFormat for all hidden rows into General.

score 1 · Answer 1 · answered Jul 18 '19 at 14:58

I have found several threads with this issue. It appears to be a limitation in Excel. Please refer to the following urls:

Error 1004 when trying to get AutoFilter criteria of date filtering

I have found several solutions that would work in my situation:

Setting ActiveWindow.AutoFilterDateGrouping = False. This will change the autofilter treeview Excel VBA Saving autofilter settings with dates.
Extracting the dates from XML: Get Date Autofilter in Excel VBA
When filtering on dates in the tree view this does not get added to criteria1 or criteria2, instead, you could use date filters.

Saving and restoring autofilter criteria, trouble with dates

1 Answers1