Save date criteria with excel VBA

Question

I'm trying to save an autofilter, to then do some things on my worksheets and re-apply the filter.

Following this topic : In Excel VBA, how do I save / restore a user-defined filter? ; it was about to work. Then I tried to filter on a date column, and it doesn't works --> It gives me the following error :

Error message

I've looking for hours on internet to found a solution, like the following post: Excel VBA Saving autofilter settings with dates; but I can't fix this problem...

I was also thinking about convert the entire column in number format, but it doesn't works as the hide cells are not converted and the filter doesn't convert the value in the filter list.

It seems like dates are not valid values to give for a Criteria1.. could it be?

Here is my code:

With myCurrentWorkbook.Worksheets(my_Of_To_Produce_Sheet).AutoFilter
        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   'it's on this line that it doesn't work
                        'I tried this solution found on internet                            
                        'filterArray(f, 1) = Replace(.Criteria1, Application.International(xlDecimalSeparator), ".")
                        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
    End With

Can you elaborate on "does't work"? Is there an error message? Does your code produce unexpected results? If so, in what way are they unexpected? — David Rushton, Aug 29 '17 at 15:00
Please, edit your question, remove the links, then come back to us with a **clear, specific** question. — dwirony, Aug 29 '17 at 15:00
@destination-data it crashes on the following line : filterArray(f, 1) = .Criteria1 — Jerry-F, Aug 29 '17 at 15:10
Do you know the value of `.Criteria1` when the code crashes? — David Rushton, Aug 29 '17 at 15:31
@destination-data No, I can't see the value, also when I try to define a variable to take the value of criteria1 (would be a variant no ?) — Jerry-F, Aug 29 '17 at 16:01
I would use a variant. Try adding 'debug.print .Criteria1` just before the offending line. If that doesn't work you can narrow it down by applying one criterion at a time to the filter. — David Rushton, Aug 29 '17 at 16:04
@destination-data Adding this line of code, it gives me the same error 1004 (as on the screenshot) on the print instruction. I think it's because the date makes a tree in the filter list (for example, multiple dates in august create a group for august). What do you think about this ? — Jerry-F, Aug 30 '17 at 07:23
I can only make this work if I disable date groupings before creating and applying the filter (`ActiveWindow.AutoFilterDateGrouping = False`). However, that makes the date filters a pain to work with. I suspect that a grouped date filter is stored in `.Criteria1` \ `.Criteria2` using an object type [VBA can not work with](https://social.technet.microsoft.com/Forums/office/en-US/b3b63a43-39b8-4109-973d-121c17b750d4/vba-autofilter-state-excel-2010?forum=excel). Looks like some sort of array. — David Rushton, Aug 30 '17 at 09:48
@destination-data You're write ! Disabling the date groups it works ; So you think it's not possible to stored this sort of array ? :( — Jerry-F, Aug 30 '17 at 12:24
There is a suggestion that this is a known [bug](https://www.experts-exchange.com/questions/28910629/VBA-to-list-the-filter-detail-of-each-worksheet-in-activeworkbook-Filtered-data.html#a41402237). — David Rushton, Aug 30 '17 at 14:26

Save date criteria with excel VBA

0 Answers0