Excel VBA Saving autofilter settings with dates

Question

I have a part of a macro that stores the autofilter settings before clearing the filter, so it can reapply them later. The code is as follows:

Dim FilterArray()
With FrontPage.AutoFilter.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
                    If .Operator = xlAnd Or .Operator = xlOr Then
                        FilterArray(f, 3) = .Criteria2
                    End If
                End If
            End If
        End With
    Next f
End With

This works fine in every situation I have come across except for date fields. In the case of date fields, Criteria1 and Criteria2, cause errors (Application-defined or object-defined error), while Operator returns a value of 7. This happened in Excel 2007 and is still happening in Excel 2013.

I assume the reason this doesn't work is due to the nested way the autofilters treat date fields, but is there a way to make this work with dates? I have seen other problems to do with applying filters to dates, but surely storing them in the first place should be easier?

And a secondary question - what does the value of 7 mean for Operator? I can only find the translation of values 0-2.

What version of Excel are you using? – Sam Dec 02 '13 at 13:16 — Sam, Dec 02 '13 at 13:16
I'm on 2013, but had the same issue with 2007 – BBaxter Dec 02 '13 at 14:21 — BBaxter, Dec 02 '13 at 14:21

score 3 · Answer 1 · answered Dec 01 '15 at 04:31

3

I found out, to enable filter Date in Array.

Below setting must be disable (Enable by default)

ActiveWindow.AutoFilterDateGrouping = False

answered Dec 01 '15 at 04:31

Eric K.

814
2
13
22

score 0 · Answer 2 · edited May 23 '17 at 12:30

I've amended your code and it runs now well also with dates in my Excel 2013:

' .Criteria1/2 are read as strings with local decimal separator
' but for re-setting Criteria1/2 should be in American format ...
' so replace local decimal separator by dot - this seems to apply also for dates, 
' because dates are also transformed internally to decimal numbers
FilterArray(f, 1) = Replace(.Criteria1, Application.International(xlDecimalSeparator), ".")

(similar for .Criteria2)

Regarding your secondary question: see code example in this post: In Excel VBA, how do I save / restore a user-defined filter?

score 0 · Answer 3 · edited May 23 '17 at 12:07

Note, this solution below is true of Excel 2010, I cannot find information on Excel 2013, but it is possible the same problem exists.
If you record a macro while running an auto date filter, you will notice the values are stored in the Criteria2 parameter, and Criteria1 is not used:

Range.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, "8/10/2015", 2, "8/20/2015")

Trying to access the Criteria2 parameter from VBA causes an "Application-defined or object-defined error".
At the time of writing this answer, the only way I know of saving this autofilter information is by reading the XML data inside the xlsx file. Working code here:
Get Date Autofilter in Excel VBA

Excel VBA Saving autofilter settings with dates

3 Answers3

Linked