Using VBA to Save and Redo Autofiltering Settings

[jonathan_wu]

I am trying to figure out how to save a worksheet's autofiltering settings and be able to re-create those settings.

I would start off with a spreadsheet with a few Autofilters on. I would like to make a macro that will record the initial Autofilter settings, then the macro would do a bunch of stuff that would need to 'Show All'. After these actions take place, I would like to be able to return to the initial Autofilter settings. I'm not even sure which VBA command should be used for this sort of thing. Can anyone point me in the right direction or provide some kind of assistance?

 

[Beate Schmitz]

Hello Jonathan,

in the German Forum I received the reply that my macro with freezing panes at the end of the custom_View-code was running without problems.

But the tester informed me that he had problems with freezing panes on a Mac lately.

I don't know whether you are using a Mac, but can't give further hints.

 

[Boller]

in the German Forum I received the reply that my macro with freezing panes at the end of the custom_View-code was running without problems.

To illustrate the problem, on a blank worksheet :

- in C3:C4 put the value "a"
- in C5 put the value "b"
- add a filter to column C
- freeze panes at C3
- filter for "b"

Then run this (not on a mac) :-

Sub custom_View()
'store settings in Custom View:
ActiveWorkbook.CustomViews.Add ViewName:="ImprobableName", _
    PrintSettings:=False, RowColSettings:=True

' here your Code
' show all
[C:C].AutoFilter Field:=1

' Restore settings:
ActiveWorkbook.CustomViews("ImprobableName").Show
ActiveWorkbook.CustomViews("ImprobableName").Delete
Range("C3").Select
ActiveWindow.FreezePanes = True

' show all
[C:C].AutoFilter Field:=1
End Sub

 

[jonathan_wu]

Thanks to everybody for all of the help. This has been a lifesaver. I implemented Peter's solution and it works like a charm.

I really appreciate all of the help. Thanks a ton.