[Answer] [Code] Retrieve Auto/Advance Filter that was applied.

[kuroba]

I wanted to give this code to people that like me had a hard time getting it to work correctly.
The 2 codes work as function in an Excel sheet and depending on parameter set, will return whatever has been filter on a particular field.

'Returns AutoFilter Criteria (Not to use on Tables)
Function ACF(FilterTitle As Range) As String
Dim str As String, c As Integer, Value As Variant
Application.Volatile
With FilterTitle.Parent.AutoFilter
With .Filters(FilterTitle.Column - .Range.Column + 1)
    If .On Then
        If IsArray(.Criteria1) Then
            For Each Value In .Criteria1
            c = c + 1
            If UBound(.Criteria1) = c Then
                str = str + Right(Value, Len(Value) - 1)
            Else
                str = str + Right(Value, Len(Value) - 1) & " | "
            End If
            Next
            Else
            str = Right(.Criteria1, Len(.Criteria1) - 1)
        End If
        If .Operator = xlAnd Or .Operator = xlOr Then str = str & " | " & Right(.Criteria2, Len(.Criteria2) - 1)
    Else
        str = "No Filter"
    End If
End With
End With
ACF = str
End Function

- The code above will return the filter applied to an Autofilter field. By adding the formula into the cell: =AFC(A1) it will return any combination of filters value applied to the cell A1.

'for Table Filter only
Function TCF(TableName As String, TableColumn As Integer) As String
Dim str As String, c As Integer, Value As Variant
Application.Volatile
With ActiveSheet.ListObjects(TableName).AutoFilter.Filters(TableColumn)
    If .On Then
        If IsArray(.Criteria1) Then
            For Each Value In .Criteria1
            c = c + 1
            If UBound(.Criteria1) = c Then
                str = str + Right(Value, Len(Value) - 1)
            Else
                str = str + Right(Value, Len(Value) - 1) & " | "
            End If
            Next
            Else
            str = Right(.Criteria1, Len(.Criteria1) - 1)
        End If
        If .Operator = xlAnd Or .Operator = xlOr Then str = str & " | " & Right(.Criteria2, Len(.Criteria2) - 1)
    Else
        str = "No Filter"
    End If
End With
TCF = str
End Function

- This code works when you have applied a table to the data group by using the formula: =TCF(TableName, TableColumn) it will do the exact same thing as the function above.

TableName: has to be in quotes IE: "Table1"
TableColumn: is the Column Number of the table 1,2,3,4 and so on.


I decided to re-post this information, as it may be helpful to someone like me that had a hard time getting it to work.


Thanks,

Kuroba