Retrieve AutoFilter sets

[sgremmo]

In a macro is correct write:

Selection.AutoFilter Field:=15, Criteria1:="=S", Operator:=xlOr, _
Criteria2:="=S2"

Is possible in a macro retrive if on a worksheet are active autofilter and with kind of sets (Fields, criterias etc.)?

Thank for help.

Simone

 

[Ivan F Moala]

Hi Simone

If I undertand you correctly then

Sub isAutofilterOn()
    MsgBox ActiveSheet.AutoFilterMode
End Sub

Function FilterCriteria(Rng As Range) As String
    Application.Volatile
    'By Stephen Bullen
    Dim Filter As String
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
            Case xlAnd
                Filter = Filter & " AND " & .Criteria2
            Case xlOr
                Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = "List is being filtered for " & Filter
End Function

With the Function you need to place a formula that refrences this function and your data range that is being Filtered for Each Column.

 

[sgremmo]

Yes, it work perfectly!

This evening, at home, I'll try on my worksheet...

Many thanks

Simone