Displaying AutoFilter criteria

http://j-walk.com/ss/excel/usertips/tip044.htm

"Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that let you specify criteria (such as "Age greater than 30"). Rows that don't match your criteria are hidden, but they are redisplayed when you turn off AutoFiltering.

'One problem with AutoFiltering is that you can't tell which criteria are in effect. Stephen Bullen developed a custom VBA worksheet function that displays the current AutoFilter criteria in a cell. The instructions that follow are for Excel 97 or later.

'Press Alt+F11 and insert a new module for the active workbook. Then enter the VBA code for the FilterCriteria shown below.

Function FilterCriteria(Rng As Range) As String
   '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 = Filter
End Function

'After you've entered the VBA code, you can use the function in your formulas. The single-cell argument for the FilterCriteria function can refer to any cell within the column of interest. The formula will return the current AutoFilter criteria (if any) for the specified column. When you turn AutoFiltering off, the formulas don't display anything.

'The figure below shows the FilterCriteria in action. The function is used in the cells in row 1. For example, cell A1 contains this formula:

=FilterCriteria(A3)
 

'As you can see, the list is currently filtered to show rows in which column A contains January, column C contains a code of A or B, and column D contains a value greater than 125 (column B is not filtered, so the formula in cell B1 displays nothing). The rows that don't match these criteria are hidden."