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?

 

[Boller]

Found this somewhere. Haven't checked it. Perhaps it will help.

Sub FilterSettings()
Dim cell As Range, crit$
Set cell = [B1]
With cell.Parent.AutoFilter
    If Intersect(cell, .Range) Is Nothing Then
        MsgBox "There is no filter in column " & cell.Column
        Exit Sub
    End If
    With .Filters(cell.Column - .Range.Column + 1)
        If Not .On Then
            MsgBox "Column " & cell.Column & " has a filter but it is not on."
            Exit Sub
        End If
        crit = .Criteria1
        Select Case .Operator
            Case xlAnd
                crit = crit & " AND " & .Criteria2
            Case xlOr
                crit = crit & " OR " & .Criteria2
        End Select
    End With
End With
MsgBox crit
End Sub

 

[Peter_SSs]

This is from the built-in Visual Basic Help from my Excel (Excel 2002). I think you should be able to get some ideas from this.

Using the Filters Collection
Use the Filters method to return the Filters collection. The following example creates a list that contains the criteria and operators for the filters in the autofiltered range on the Crew worksheet.

Dim f As Filter
Dim w As Worksheet
Const ns As String = "Not set"

Set w = Worksheets("Crew")
Set w2 = Worksheets("FilterData")
rw = 1
For Each f In w.AutoFilter.Filters
    If f.On Then
        c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
        If f.Operator Then
            op = f.Operator
            c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)
        Else
            op = ns
            c2 = ns
        End If
    Else
        c1 = ns
        op = ns
        c2 = ns
    End If
    w2.Cells(rw, 1) = c1
    w2.Cells(rw, 2) = op
    w2.Cells(rw, 3) = c2
    rw = rw + 1
Next

 

[Beate Schmitz]

Hello,

use Custom Views in your code:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> custom_View()
    <span style="color:#008000"; >'store settings in Custom View:</span>
    ActiveWorkbook.CustomViews.Add ViewName:=<span style="color:#800000"; >"ImprobableName"</span>, _
        PrintSettings:=False, RowColSettings:=True
    <span style="color:#008000"; >' here your Code</span>
    <span style="color:#008000"; >' Restore settings:</span>
    ActiveWorkbook.CustomViews(<span style="color:#800000"; >"ImprobableName"</span>).Show
    ActiveWorkbook.CustomViews(<span style="color:#800000"; >"ImprobableName"</span>).Delete
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span>
</span></nobr></div>

 

[jonathan_wu]

Thanks for the fast replies. I'm using Beate's solution and it works great, only I have one problem. I am using frozen panes anchored at C, 3 and using this process, if C, 3 is not visible with the current filters, it anchors it at the point closest to C, 3 which, in turn, can really mess up the panes' frozen positions.

Thanks again for all the help.

 

[Beate Schmitz]

Hello Jonathan,

upon your reply I understood your problem.

Change my code as follows:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> custom_View()
    <span style="color:#008000"; >'store settings in Custom View:</span>
    ActiveWorkbook.CustomViews.Add ViewName:=<span style="color:#800000"; >"ImprobableName"</span>, _
        PrintSettings:=False, RowColSettings:=True
    <span style="color:#008000"; >' here your Code</span>
    <span style="color:#008000"; >' Restore settings:</span>
    ActiveWorkbook.CustomViews(<span style="color:#800000"; >"ImprobableName"</span>).Show
    ActiveWorkbook.CustomViews(<span style="color:#800000"; >"ImprobableName"</span>).Delete
    Range(<span style="color:#800000"; >"C3"</span>).Select
    ActiveWindow.FreezePanes = <span style="color:#000080"; >True</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span>

</span></nobr></div>

 

[jonathan_wu]

Thanks, but I've tried this and if the cell C3 is not visible because of the previous filters, it will freeze panes at the closest cell in the column. For instance, if, previous to saving the custom view, rows 3, 4, and 5 were not visible, then, when it sets the filters back, it will freeze panes beginning at C6 because that was the closest visible cell.

 

[Beate Schmitz]

Hello,

as I wrote, my solution runs under my Excel2003. Pls have some patience and watch the threat. I'm asking in German forum under
http://www.online-excel.de/fom/fo_read.php?f=1&bzh=0&h=21236&ao=1#a123x
for a solution, because I'm interested in a overall working solution too. If I have a positive reply I will revert.

 

[Boller]

Presumably the filters are in row 1.

Why not put them in row 2?
Alternatively, keep row 3 blank, put the filters in row 3, and freeze at C4.

 

[Peter_SSs]

This is going back to the idea of individually capturing the AutoFilter settings and restoring them, but may solve the Freeze Panes issue.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ReDoAutoFilter()
    <SPAN style="color:#00007F">Dim</SPAN> w <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> filterArray()
    <SPAN style="color:#00007F">Dim</SPAN> currentFiltRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> w = ActiveSheet
    
    <SPAN style="color:#007F00">' Capture AutoFilter settings</SPAN>
    <SPAN style="color:#00007F">With</SPAN> w.AutoFilter
        currentFiltRange = .Range.Address
        <SPAN style="color:#00007F">With</SPAN> .Filters
            <SPAN style="color:#00007F">ReDim</SPAN> filterArray(1 <SPAN style="color:#00007F">To</SPAN> .Count, 1 <SPAN style="color:#00007F">To</SPAN> 3)
            <SPAN style="color:#00007F">For</SPAN> f = 1 <SPAN style="color:#00007F">To</SPAN> .Count
                <SPAN style="color:#00007F">With</SPAN> .Item(f)
                    <SPAN style="color:#00007F">If</SPAN> .On <SPAN style="color:#00007F">Then</SPAN>
                        filterArray(f, 1) = .Criteria1
                        <SPAN style="color:#00007F">If</SPAN> .Operator <SPAN style="color:#00007F">Then</SPAN>
                            filterArray(f, 2) = .Operator
                            filterArray(f, 3) = .Criteria2
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> f
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#007F00">'Remove AutoFilter and Frozen Panes</SPAN>
    w.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN>
    ActiveWindow.FreezePanes = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#007F00">' Your code here</SPAN>
    
    <SPAN style="color:#007F00">' Freeze panes at C3</SPAN>
    Range("C3").Select
    ActiveWindow.FreezePanes = <SPAN style="color:#00007F">True</SPAN>
    
    <SPAN style="color:#007F00">' Restore Filter settings</SPAN>
    <SPAN style="color:#00007F">For</SPAN> col = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(filterArray(), 1)
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(filterArray(col, 1)) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> filterArray(col, 2) <SPAN style="color:#00007F">Then</SPAN>
                w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1), _
                        Operator:=filterArray(col, 2), _
                    Criteria2:=filterArray(col, 3)
            <SPAN style="color:#00007F">Else</SPAN>
                w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1)
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> col
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>