Using VBA to Save and Redo Autofiltering Settings

jonathan_wu

New Member
Joined
Aug 8, 2007
Messages
4
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Found this somewhere. Haven't checked it. Perhaps it will help.

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Active Member
Joined
May 20, 2007
Messages
392
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

New Member
Joined
Aug 8, 2007
Messages
4

ADVERTISEMENT

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

Active Member
Joined
May 20, 2007
Messages
392
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

New Member
Joined
Aug 8, 2007
Messages
4

ADVERTISEMENT

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.
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top