Capture Autofilter state

dz72zc

New Member
Joined
May 18, 2006
Messages
10
Hi,

I use Excel 2007 and I have a spreadsheet with data in column a thru O and 300 rows with Autofilter turned on for all columns and buttons with code behind them.

I'm trying to capture the state of Autofilter to be able to revert back to it. In more details here is what I do: I use Autofilter to narrow down the data, I click on a button to perform an action but the code behind that button does a find in the whole spreadsheet but finds nothing as the data it looks for is filtered out. So I would like to capture the state of the Autofilter, remove any filters, do the search, and reapply the autofilter to bring me back to the set of data I had before I pushed the button. Of course I never filter on the same thing so I cannot have code to just apply the same filter all the time.

Any help would be great.
Thanks
Louis
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
This will turn off Autofilter if one exists:
Code:
ActiveSheet.AutoFilterMode = False
To turn the Autofilter back on, be sure to include at least one cell in the range you want to filter.
Code:
ActiveSheet.Range("A1").AutoFilter
I do not have Excel 2007, but I believe this should work for you.
 
Upvote 0
Thanks Datsmart but this is not doing exactly what I wanted. What you provided turn off Autofilter and turn it back on without any filter but what I want is that when I turn it back on it to go back to the same filtered data I had before I started the macro. Sorry if I was not clear enough.

So basically I start with a subset of my 300 rows, I run the macro which needs to turn off the filter to find the data and then when the macro exit, It bring me back showing me the same filtered data I started with. That's what I would like to do.... Any ideas?

Louis
 
Upvote 0
Geez, the code I have that would help is at the office and I'm home...

Basically you're going to have to loop across each row header (assuming you've applied John's test and Autofilter is indeed on).

Then you'll need to test to see what filter, if any is active. Look at the Autofilter object in the object browser in the VBE. You might also have a look at Chip's conditional formatting functions here:
http://www.cpearson.com/Excel/CFColors.htm

Conceptually determining the active filter is kind of along the same lines.

HTH,
 
Upvote 0
I haven't tried this with excel 2007, but hopefully will do what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ReDoAutoFilter()<br>    <SPAN style="color:#00007F">Dim</SPAN> w <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> filterArray()<br>    <SPAN style="color:#00007F">Dim</SPAN> currentFiltRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> w = ActiveSheet<br>    <br>    <SPAN style="color:#007F00">' Capture AutoFilter settings</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> w.AutoFilter<br>        currentFiltRange = .Range.Address<br>        <SPAN style="color:#00007F">With</SPAN> .Filters<br>            <SPAN style="color:#00007F">ReDim</SPAN> filterArray(1 <SPAN style="color:#00007F">To</SPAN> .Count, 1 <SPAN style="color:#00007F">To</SPAN> 3)<br>            <SPAN style="color:#00007F">For</SPAN> f = 1 <SPAN style="color:#00007F">To</SPAN> .Count<br>                <SPAN style="color:#00007F">With</SPAN> .Item(f)<br>                    <SPAN style="color:#00007F">If</SPAN> .On <SPAN style="color:#00007F">Then</SPAN><br>                        filterArray(f, 1) = .Criteria1<br>                        <SPAN style="color:#00007F">If</SPAN> .Operator <SPAN style="color:#00007F">Then</SPAN><br>                            filterArray(f, 2) = .Operator<br>                            filterArray(f, 3) = .Criteria2<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> f<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Remove AutoFilter</SPAN><br>    w.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">' Your code here</SPAN><br>    <br>    <SPAN style="color:#007F00">' Restore Filter settings</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> col = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(filterArray(), 1)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(filterArray(col, 1)) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> filterArray(col, 2) <SPAN style="color:#00007F">Then</SPAN><br>                w.Range(currentFiltRange).AutoFilter field:=col, _<br>                Criteria1:=filterArray(col, 1), _<br>                Operator:=filterArray(col, 2), _<br>                Criteria2:=filterArray(col, 3)<br>            <SPAN style="color:#00007F">Else</SPAN><br>                w.Range(currentFiltRange).AutoFilter field:=col, _<br>                Criteria1:=filterArray(col, 1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> col<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
Thanks Peter and Greg for your replies. Peter, I notice what you replied is the code provided in the online help. I am not sure why I did not look there in the first place but I did not. So I tried the code and sometimes it works and sometimes it does not. The reason is because Excel 2007 has more advanced filtering capabilities and this macro does not account for these new possibilities. In Excel 2003 you could simply filter on 2 criteria at the most joined by an operator (and/or with corresponding values or 1 or 2 for .Operator) and the macro does well with that but in 2007 you can also filter on as many individual records as you want in a checkoff box style selection. At that time the value of .Operator becomes 7 and the value of .Criteria1 hence filterArray looks like this in a watch window:
filterArray(f,1)(1) "=Value1"
filterArray(f,1)(2) "=Value2"
filterArray(f,1)(3) "=Value3"
filterArray(f,1)(4) "=Value4"
filterArray(f,1)(5) "=Value5"
filterArray(f,1)(6) "=Value6"
filterArray(f,1)(7) "=Value7"
filterArray(f,1)(8) "=Value8"

and .Criteria2 contains nothing. You can even filter on cell colors and at that time .Operator value is 8 and the macro fails because .Criteria1 contains nothing. And I'm sure there are other conditions I am not thinking of right now that will stop me dead in my tracks.

So if someone would have some code that could point me in the right direction I would appreciate and if not I will post what I come up with in this thread.

Louis
 
Upvote 0
here are some correction for MS Office 2007...

Sub ReDoAutoFilter(ws As Worksheet)
Dim filterArray()
Dim currentFiltRange As String
Dim i As Integer

' Capture AutoFilter settings
With ws.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For i = 1 To .Count
With .Item(i)
If .On Then
filterArray(i, 1) = .Criteria1
If .Operator Then
filterArray(i, 2) = .Operator
If .Operator = xlAnd Or .Operator = xlOr Then
filterArray(i, 3) = .Criteria2
End If
End If
End If
End With
Next i
End With
End With

'Remove AutoFilter
ws.AutoFilterMode = False

' Restore Filter settings
For i = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(i, 1)) Then
If filterArray(i, 2) Then
'check if Criteria2 exists and needs to be populated
If filterArray(i, 2) = xlAnd Or filterArray(i, 2) = xlOr Then
ws.Range(currentFiltRange).AutoFilter Field:=i, _
Criteria1:=filterArray(i, 1), _
Operator:=filterArray(i, 2), _
Criteria2:=filterArray(i, 3)
Else
ws.Range(currentFiltRange).AutoFilter Field:=i, _
Criteria1:=filterArray(i, 1), _
Operator:=filterArray(i, 2)
End If
Else
ws.Range(currentFiltRange).AutoFilter Field:=i, _
Criteria1:=filterArray(i, 1)
End If
End If
Next i
End Sub
 
Upvote 0
I know this is an old thread but it is the only one I could find that is close to my problem. (I have also opened a new thread at http://www.mrexcel.com/forum/showthread.php?p=2265957#post2265957


Does anyone know if it is possible to discover the criteria used by the user to save and restore the state of an autofilter if Operator:= xlFilterValues?

When I attempt to record a macro to create an autofilter as follows:

start snippet<SNIPPET>:

Sub Macro2()
'
' Macro2 Macro
'
'
Range("D3").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= Array("1 - Identify", "2 - Qualified/Contacted", "6 - Close"), Operator:= xlFilterValues
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Operator:= xlFilterValues, Criteria2:=Array(0, "9/30/2010", 1, "4/28/2009", 1, "5/1/2009", 1, "10/26/2009", 2, "12/18/2009", 2, "12/31/2009")
End Sub


end snippet</SNIPPET>

I can (of course) replay this macro to obtain the same autofilter result as the first time I used the autofilter (to create the recorded macro). However, if I attempt to use the saveautofilter and restoreautofilter functionality as presented by "Peter SSs" in this thread (I have used similar functionality successfully in Excel 2003 for a few years), when I try to access the value of Criteria1 (and Criteria2 if it exists), I receive error 1004 (the wonderfully generic "application-defined or object-defined error").

As expected, attempting to read the values stored in Criteria1 and Criteria2 using the watch window is impossible too. For now, I am checking if the .Operator value = xlFilterValues and, if it is, do nothing.

If anyone has any method to discover or read the filter values referenced via the complex filter that exists if .Operator = xlFilterValues, I would love to hear about it.

Thanks,

Steve
 
Last edited:
Upvote 0
Hello

Thanks to Peter again for the "filters restitution" snippet that completes the memorization snippet provides by the Excel VBA help.

It works with Excel 2007 but limitedly since Excel 2007 offers selection of multiple values in one filter.


bye
 
Upvote 0
This is essentially the same as lr_ but works in all these cases
- AutoFiltered (but not active)
- AutoFilter active
- Not AutoFiltered
Also, it hides screen updates.



Private Sub cbSplit_Click()
Dim ws As Worksheet
Dim filterArray()
Dim currentFiltRange As String
Dim col As Integer

Application.ScreenUpdating = False
Set ws = ActiveSheet
'Capture AutoFilter settings
If ws.AutoFilterMode = True Then
With ws.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For col = 1 To .Count
With .Item(col)
If .On Then
filterArray(col, 1) = .Criteria1
If .Operator Then
filterArray(col, 2) = .Operator
If .Operator = xlAnd Or .Operator = xlOr Then
filterArray(col, 3) = .Criteria2
End If
End If
End If
End With
Next col
End With
End With
End If

'Remove AutoFilter
ws.AutoFilterMode = False

'Your code here

'Restore Filter settings
If Not currentFiltRange = "" Then
ws.Range(currentFiltRange).AutoFilter
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If filterArray(col, 2) Then
'check if Criteria2 exists and needs to be populated
If filterArray(col, 2) = xlAnd Or filterArray(col, 2) = xlOr Then
ws.Range(currentFiltRange).AutoFilter Field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
ws.Range(currentFiltRange).AutoFilter Field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2)
End If
Else
ws.Range(currentFiltRange).AutoFilter Field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next col
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top