Looking for Excel Macro to Show Active Filter Values

[alex20850]

I remember seeing an Excel macro in a book or online that showed the values in filters in use with Autofilter on a line about the column headings. Would love to find it again.

 

[xlbo]

Maybe try looking in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[alex20850]

I found what I was looking for at:

http://www.ozgrid.com/VBA/autofilter-criteria.htm

It creates a AutoFilter_Criteria function that you can use to display the filter in use.

[

http://www.ozgrid.com/images/Pictures/autofilt_criteria.jpg

]

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String
    Application.Volatile
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then Exit Function
                strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If
        End With
    End With
    AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function