Error 1004 when trying to get AutoFilter criteria of date filtering

Question

I'm working on a piece of VBA code which should save & restore the current AutoFilter state in Excel. I've been using the code here for a looong time without any issues, but now I've run into a pretty serious one. Let me illustrate that...

Assume you have a (very simple) table setup with date filtering: enter image description here

If you would like to get the filtering criteria used programmatically, it will fail: enter image description here

This is happening for me in Excel 2010. Does anyone know a workaround for this?

A link for the same issue described by someone else on Microsoft TechNet: Excel VBA AutoFilter criteria when Operator is xlFilterValues for Dates This points to Jon von der Heyden's site for a solution, but the site mentions:

Unknown (likely date TreeView filter): To-date I am unable to find a way to capture date filters where the criteria is based on selection from the Tree View control in the Filter drop-down. These criteria are not stored in the Criteria1 or Criteria2 properties. I imagine working out the criteria will involve looping the Range_Field values. Although this would first require that all other field filters be turned-off, something not do-able from a worksheet function and would involve a timer to trigger a sub-routine. Again, I chose to avoid the extra complexity.

@Gary'sStudent You can fully reproduce the issue with that one line visible on the second screenshot - that in itself will throw the error below, while it shouldn't. — Scorchio

Mill Mill · Accepted Answer · 2015-11-19T16:27:59

An alternative way to store the autofilter state (including the treeview selection of date filters) is to use a custom view.

'[whatever code you want to run before capturing autofilter settings]

    wkbExample.CustomViews.Add ViewName:="cvwAutoFilterSettings", RowColSettings:=True
    
    '[whatever code you want to run with either your autofilter or no autofilter]
    
    wkbExample.CustomViews("cvwAutoFilterSettings").Show
    wkbExample.CustomViews("cvwAutoFilterSettings").Delete
    
    '[whatever code you want to run after restoring original autofilter settings]

Original post by Cyious

However there are three conditions to make it work:

None of the sheets in the workbook should be protected
The fields to which the autofilter was applied should not be cleared/deleted before showing the custom view
You can not have any ListObjects (i.e. ranges "formatted as table") on any sheet in your workbook. If you have at least one ListObject, the CustomViews feature is disabled altogether and any VBA call to it will cause runtime error 1004. You would have to .UnList all of your ListObjects before saving the custom view.

Bonus: also column width, freeze pane position and range location within the window are stored in the custom view.

Error 1004 when trying to get AutoFilter criteria of date filtering

3 Answers