Capture Autofilter state

[dz72zc]

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

 

[Datsmart]

This will turn off Autofilter if one exists:

ActiveSheet.AutoFilterMode = False

To turn the Autofilter back on, be sure to include at least one cell in the range you want to filter.

ActiveSheet.Range("A1").AutoFilter

I do not have Excel 2007, but I believe this should work for you.

 

[dz72zc]

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

 

[Greg Truby]

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,

 

[Peter_SSs]

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>

 

[dz72zc]

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

 

[lr_]

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

 

[smedz0713]

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

 

[MrJoeExcel]

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

 

[jkmblogs]

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