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

 

[crabman27]

@jkmblogs and @lr_: Thank you so much for your solutions. I've looked everywhere for this, and found snippets that showed promise but didn't work. Your code works perfectly for me.

 

[blaksnm]

I think this "saves" the filtercriteria in active sheet. Right?
How can I use this saving to filter all other sheets in my workbook (sheetname beginning with "rev"?

 

[blaksnm]

Thanks everyone for mhelp. Problem solved by your help

 

[tonyr63]

Hello

I have found trying to automate Excel filters very challenging. I wrote code to copy out filter elements to separate sheets however found that it was difficult to get it in every scenario to output the correct criteria in cases where more than one filter is on.

I reviewed the code here very carefully trying to follow what the Array was storing but found it was not consistent.

I can see that the macro works as intended but cannot see how.

With my testing I had filters on in column D and F (filter 4 and 6) but stepping through the code -
If .On Then filterArray(f, 1) = .Criteria1 was not picking up any value - debug.Print filterArray (4,1) was showing error 13 even though Criteria were selected and filtered on filter on my sheet. How come the error 13 did not halt the code at runtime?
Yet filterArray (6,1) picks up its criteria properly? Go figure. When I paused the Macro half way through its processing I was seeing variables in very unexpected states including type mismatch errors that did not halt the code even though there was no On Error Resume Next statements.

If I run to the End with and pause it shows Subscript out of range on the Array which can be resolved f = 1 to .Count-1

Debug.Print filterarray(4,1) returns Error 13, debug.Print filterarray(4,2) returns 7 as expected, debug.Print filterarray(6,1) returns proper Criteria =name so why does (4,1) not also return its corresponding criteria =name?

Both filters 4 and 6 which are on are logically the same filtering names however treated differently
by the Macro that actually works. How is it working?

I am totally confused to understand how filtering has been implemented in Excel and it needs to be redesigned from the ground up and not patched on top of patches.

 

[deecee]

Just found this thread on autofilter saving which does 99% of what I need. Hope it still gets read. However, I'd like to save the settings between sessions. Other than writing everything out to another worksheet and re-reading in a few days later, does anyone have a way of saving the info to an external file?

 

[Jules_A]

I notice this is only applicable for the first few columns, how would you change this code to not stop at column 3, but to go until column 10?

 

[warbe]

Saving and restoring autofiltering can be achieved by the following few lines of vba code:

Dim cv As CustomView

   If .ActiveSheet.AutoFilterMode = True Then
      Set cv = .CustomViews.Add(ViewName:="any unique name", RowColSettings:=True)
      .ActiveSheet.AutoFilterMode = False
   End If

   ' any code here

   If Not cv Is Nothing Then
      cv.Show
      cv.Delete
      Set cv = Nothing
   End If

Compared to other posted solutions this one appears not only outperformingly simple but also safe and effective with any excel verison.
By the way also hidden columns may be displayed in the 'any code here and cv.Show will return to the hidden columns state without any extra effort. Nevertheless, a really comprehensive solution may rather look like this:

Option Explicit

Public Enum xlOnOff
    xlOn
    xlOff
End Enum

Public Sub Filtering(ByVal OnOff As xlOnOff)
' ------------------------------------------
' Common procedure to set filtering off and
' back on in any case where filtering when
' active prevents operations, e.g. move/copy
' rows.
' xlOnOff = xlOff: If filtering is active a
'                  CustomView is created and
'                  filtering is turned off.
' xlOnOff = xlOn:  Return to the CustomView
'                  created with xlOff.
' Note: When filtering is not active xlOff
'       and xlOn do nothing. Thus, no need
'       to check for an active filtering.
'
' W. Rauschenberger Berlin April 2019
' ------------------------------------------
Const sCv   As String = "cvTempNameWhenFiltered"
Static cv   As CustomView
Dim wbk     As Workbook


    Set wbk = ThisWorkbook
    Select Case OnOff
        Case xlOff
            With wbk
                If .ActiveSheet.AutoFilterMode = True Then
                    Set cv = .CustomViews.Add(ViewName:=sCv, RowColSettings:=True)
                    .ActiveSheet.AutoFilterMode = False
                End If
            End With
        Case xlOn
            If Not cv Is Nothing Then
                '~~ Return to the CustomView saved with xlOff in order to
                '~~ re-establish filtering with its original properties.
                cv.Show
                cv.Delete
                Set cv = Nothing ' otherwise, object would still exist but point to nowhere
            End If
    End Select

End Sub