Check status of current filter mode

[ebrandt]

I need to understand the syntax and code to determine what filters are currently applied to a spreadsheet so that I can return the filters to the sheet at the end of my macro.

I currently have a macro I created to copy and insert a row of data identical to the row your cursor is on. The first step of the macro is to unfilter the data since Excel doesn’t copy insert well with filtered data. This has confused some users because after they run the macro their data and screen looks different.

This is my current code;

Sub InsertNewRow()
' InsertNewRow Macro
' Macro recorded 1/25/2006 by Ebrandt
' Keyboard Shortcut: Ctrl+a
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
    On Error Resume Next
    ActiveSheet.ShowAllData
    ActiveCell.EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
 
End Sub

I need to understand the syntax and code to determine what filters are currently applied to a spreadsheet so that I can return the filters to the sheet at the end of my macro.

 

[igold]

Does this help you...

Sub GetFilters()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim filt As Filter
    Dim ct As Long
    Dim fs As String
    
    If ws.AutoFilter Is Nothing Then Exit Sub
    For Each filt In ws.AutoFilter.Filters
        If filt.On Then
            If filt.Count = 1 Then
                fs = fs & filt.Criteria1
                GoTo nextfilt
            End If
            On Error Resume Next
            For ct = 1 To filt.Count
                fs = fs & filt.Criteria1(ct)
            Next
            If Not filt.Criteria2 = "" Then
                fs = fs & filt.Criteria1
                fs = fs & filt.Criteria2
            End If
        End If
nextfilt:
    Next
    fs = Replace(fs, "=", vbNewLine)
    MsgBox "The following filters are in use:" & _
        vbNewLine & fs
        
End Sub

 

[ebrandt]

That does help. Thank you.

I failed to mention that at the end of my macro I need to reapply the filter.

Do you have the code to do that?

 

[John_w]

I'm not sure how well this code will work with your data and filters (AutoFilter, not Advanced Filter). I've tested it on a few simple AutoFilters and it works for me.

There are 2 routines: Get_Current_AutoFilters, which looks at the AutoFilters on the specified sheet and returns an array of the filter settings; Restore_AutoFilters, which reapplies the filter settings.

Public Function Get_Current_AutoFilters(filterSheet As Worksheet) As Variant

    Dim f As Long
    Dim filt As Filter
    
    'Return an array of the AutoFilter settings
    
    If Not filterSheet.AutoFilter Is Nothing Then
        With filterSheet.AutoFilter
            With .Filters
                ReDim filtersArray(1 To .Count, 1 To 3)
                For f = 1 To .Count
                    Set filt = .Item(f)
                    With filt
                        If .On Then
                            filtersArray(f, 1) = .Criteria1
                            If .Operator Then
                                filtersArray(f, 2) = .Operator
                                On Error Resume Next
                                filtersArray(f, 3) = .Criteria2
                                On Error GoTo 0
                            End If
                        End If
                    End With
                Next
            End With
        End With
        Get_Current_AutoFilters = filtersArray
    End If
    
End Function


Public Sub Restore_AutoFilters(savedAutoFilterRange As Range, savedAutoFilters As Variant)

    Dim f As Long
        
    'Restore the AutoFilter settings

    For f = 1 To UBound(savedAutoFilters)
        If Not IsEmpty(savedAutoFilters(f, 1)) Then       'Criteria1
            If IsEmpty(savedAutoFilters(f, 2)) Then       'Operator
                savedAutoFilterRange.AutoFilter Field:=f, Criteria1:=savedAutoFilters(f, 1)
            Else
                If IsEmpty(savedAutoFilters(f, 3)) Then   'Criteria2
                    savedAutoFilterRange.AutoFilter Field:=f, Criteria1:=savedAutoFilters(f, 1), Operator:=savedAutoFilters(f, 2)
                Else
                    savedAutoFilterRange.AutoFilter Field:=f, Criteria1:=savedAutoFilters(f, 1), Operator:=savedAutoFilters(f, 2), Criteria2:=savedAutoFilters(f, 3)
                End If
            End If
        Else
            savedAutoFilterRange.AutoFilter Field:=f
        End If
    Next
    
End Sub

You call Get_Current_AutoFilters before clearing the current filters (ShowAllData) and call Restore_AutoFilters after, for example:

Public Sub Save_Clear_Restore_AutoFilters()

    Dim filterSheet As Worksheet
    Dim currentAutoFilterRange As Range
    Dim currentAutoFilters As Variant
    
    'This routine will operate on the active sheet
    
    Set filterSheet = ActiveSheet
    
    'Save the current AutoFilter range and the settings for each AutoFiltered column (field)
    
    Set currentAutoFilterRange = filterSheet.AutoFilter.Range
    currentAutoFilters = Get_Current_AutoFilters(filterSheet)
    
    If Not IsEmpty(currentAutoFilters) Then
    
        'Remove the AutoFilter by showing all the data
        
        filterSheet.ShowAllData
        
        'Restore the saved AutoFilter settings
        
        Restore_AutoFilters currentAutoFilterRange, currentAutoFilters
        
    End If
    
End Sub

 

[ebrandt]

Thanks. I'm trying to work with this.