Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Saving and Restoring AutoFilter Settings (Tested on XL97)

    I am adding this reply as a new thread since I feel it is more than a continuation of the original thread, but is actually a separate topic that deserves its own thread.

    The original post <post#=231610>post 231610</post#> dealt with making sure that a list was not filter BEFORE using the technique of last cell, endup to find the last row. A question was posed <post#=232945>post 232945</post#> on how to restore the settings after removing the filter.

    The following code should allow saving and then restoring an Autofilter.

    If you use the top/bottom item/percent list it will NOT restore the VALUE you put in (eg "top 2 %") since I found no place where excel stored that value that was accessible in VB. Excel seems to take the entry (eg "top 2 %") and then calculate the ">= value" for the criterion and saves that as a custom entry. So even though that value is NOT restored the list is restored to the way it was filtered before.

    Perhaps someone else can shed some light on this.

    I would imagine it being used in code something like this:
    <pre>sub YourRoutine()
    'run some code

    SaveAutoFilterSettings
    If wks.FilterMode Then wks.ShowAllData

    'runsome more code here

    RestoreAutoFilterSettings

    'maybe even run some more code

    End sub
    </pre>


    The routines I wrote use the filters collection. You can add this to a module and call as desired.

    Hope this is useful,
    Steve
    <pre>Option Explicit
    Option Base 1
    Public vFilterArray()
    Public wks As Worksheet
    Public f As Filters
    </pre>


    <pre>Sub SaveAutoFilterSettings()
    Dim msg As String
    Dim x As Long, y As Long

    Set wks = ActiveSheet
    If wks.AutoFilterMode = False Then Exit Sub

    Set f = wks.AutoFilter.Filters
    ReDim vFilterArray(f.Count, 4)

    For x = 1 To f.Count
    For y = 1 To 4
    vFilterArray(x, y) = False
    Next y
    Next x

    For x = 1 To f.Count
    If f(x).On Then
    vFilterArray(x, 1) = True
    vFilterArray(x, 2) = f(x).Criteria1

    If f(x).Operator Then
    If f(x).Operator = xlAnd Or _
    f(x).Operator = xlOr Then
    vFilterArray(x, 3) = f(x).Operator
    vFilterArray(x, 4) = f(x).Criteria2
    End If
    End If
    End If
    Next x
    End Sub
    </pre>


    <pre>Sub RestoreAutoFilterSettings()
    Dim x As Long
    Dim rng As Range
    Set rng = Range(f.Parent.Range.Address)

    For x = 1 To f.Count
    If vFilterArray(x, 1) And _
    (vFilterArray(x, 3) <> False) Then

    rng.AutoFilter field:=x, _
    Criteria1:=vFilterArray(x, 2), _
    Operator:=vFilterArray(x, 3), _
    Criteria2:=vFilterArray(x, 4)

    ElseIf vFilterArray(x, 1) Then

    rng.AutoFilter field:=x, _
    Criteria1:=vFilterArray(x, 2)

    Else
    rng.AutoFilter field:=x

    End If
    Next x
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving and Restoring AutoFilter Settings (Tested on XL97)

    Steve,

    Nice code! Custom Views (in the View menu) are also a good way to save and restore filter settings. I performed a small, non-exhaustive test - Custom Views seem to store *all* AutoFilter settings, incl. Top/Bottom settings. So you could perhaps use code like this:

    ' Store filter settings in Custom View.
    ActiveWorkbook.CustomViews.Add ViewName:="ImprobableName", _
    PrintSettings:=False, RowColSettings:=True

    ' Turn off AutoFilter here and do whatever is needed.
    ...

    ' Restore settings.
    ActiveWorkbook.CustomViews("ImprobableName").Show

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving and Restoring AutoFilter Settings (Tested on XL97)

    I hadn't thought about VIEWS. I will have to look more at that.

    I worked thru the code, not only to answer the poster's question, but also to get a better feel (for myself) for AUTOFILTER and the FIlters collection. Personally, I learn best when I have to struggle to find a solution: once you understand a tool better, you start thinking of other things to do with it.

    Thanks for another approach. I have always heard that there are at least 3 ways to do everything in excel, I guess we'll have to see, if anyone comes up with something else.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving and Restoring AutoFilter Settings (Tested on XL97)

    This link shows how to _display_ the autofilter settings, handy to document them in printouts.
    http://j-walk.com/ss/excel/usertips/tip044.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •