Saving Autofilters - Dates

[jordan-s]

Hi,

What I have is a sheet filled with data, which relies heavily on autofilters.

At some point I dump new data into the sheet, attempting to keep the same filters.


I'm currently just saving the criteria and operator by storing them in global variants

Please Login or Register  to view this content.

However, when dealing with a date the .on is true but the criteria both return an application or object error.

This is pretty easy to reproduce, simply create a small table of data containing at least 1 column of dates, then try to exclude a single date.


1 solution would be to use Custom Views, however this is a pretty complex workbook and currently relies on multiple lists.


Anyone know how I can get the criteria for date-filters?

[p24leclerc]

Have you looked at the note about dates filter?

AutoFilter Object (Excel)

Represents autofiltering for the specified worksheet.

Note
When using AutoFilter with dates, the format should be consistent with English date separators ("/") instead of local settings ("."). A valid date would be "2/2/2007", whereas "2.2.2007" is invalid.

Could it be your problem? I didn't test it though.

[jordan-s]

Have you looked at the note about dates filter?

Could it be your problem? I didn't test it though.

I'm not sure that this is relevant to me?

The user defines the auto-filter,
so I dont actually define the format of the date. All Im trying to do is retrieve and save it (or at least at this point).

[jordan-s]

Still keeping an eye out for a solution. Considering one of 2 options at this point:

1. Replace all Lists in the workbook with custom implementations, then use Custom Views
-Down side is that there are at least 5 lists being used, with heavy use of VBA to alter them on the fly.

2. Create a custom control that reproduces the 2010 date filter functionaility and apply it to each of the date columns. Then use the data gained from this control to apply the filter to the worksheet.

Just getting access to getting the filter data after the fact would still be the simplest option for me at this point if it was available (or second simplest if you were able to use custom views and lists in the same workbook).

Anyone have any advice re-retrieving the date-filter data?