Remembering the current filter in Excel 1

[bluegnu]

Hi,

Is there a way to remember the current filter settings to reapply them later. For instance, I have Column C filtered by "Tea" & "Coffee" and Column E filtered by "Milk". I want to unfilter everything, but before I do that I want to remember the tea coffee milk filter to reapply it later.

Can this be done?

thanks

 

[SkipVought]

Hi,

Can this be done?

Yes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

Thanks Skip, maybe I should elaborate on my vagueness. Ordinarily I would record a macro and disect it to see how theVBA works, but I can't do that here so would you be able to point me in a better direction as to how to do this?

 

[SkipVought]

Why can you NOT record a macro?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

How could you record a macro to remember the filter settings?

 

[SkipVought]

Every journey starts with a single step.

First record setting the filter.

Then we need to talk about how the process would work, because there is no EVENT associated with changing the AutoFilter. I think you'll have to do some fancy designing and codeing, including

1. Create UNIQUE LISTS for each column that will be filtered.
2. HIDE the row containing the filters and, instead, insert a ComboBox
3. use the worksheet_change event when the Combobox puts a new value into the cell to trigger the filter.

This will not be a trivial task!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

How could you record a macro to remember the filter settings?

The code here:

http://www.ozgrid.com/VBA/autofilter-criteria.htm

will display the current criteria in a cell. You could use as is then save those criteria (copy, pastespecial, values..).



Gavin

 

[Gavona]

Hmm, the Application.volatile line in that code ensures that the cell is recalculated whenever the sheet is recalculated. But it appears from google that it may create a performance issue.
Maybe delete that line of the code but explicitly calculate the formulae at the appropriate time [ range(...).calculate ].


Gavin

 

[SkipVought]

Getting the filter values is not the major issue.

It is, how do you trigger the event?

The fact is that the PROGRAM will have to set the filters, otherwise, if Whosit sets the filter, HOW DO YOU KNOW THAT??? Its a stealth, non-event.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Appreciate that Skip but I don't think it is clear that the op wanted any more than that. Maybe he is happy to manually run a macro.
Also, in brief testing the formula results changed whenever I changed the filter. This is the purpose of application.volatile So I assume that the calculate event is firing.
In case this proves unreliable or dependent on excel version then force a calculate event by having a formula on the sheet referencing the visible cells in the database area.


Gavin

 

[Gavona]

Damn the lack of edit facility. I meant to exemplify the last sentence with =subtotal(2,.......).


Gavin

 

[SkipVought]

So I assume that the calculate event is firing.

Well it really is! Thanks for the link.
[purple]
***[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Thanks Skip! I do have the impression that different things fire the calculate event in different versions of Excel but the Subtotal solution should fix if that proves a problem.

I still can't see an easy way to the full solution in the way (I imagine) you were envisaging. The calculate event will be prone to fire more often than the filter values change. And one might change the filter values in several columns before wanting to return to the original. How to differentiate the original from the intermediate filter states?

That said the op was merely about having a macro that:
Saves current settings
Did Data,Filter,ShowAll

and another that Restored original filter settings

Gavin