FREE Excel STUFF
SearchSearch Excel Content
Excel Help. Popular
NEW! Multiple Excel Search & Links
Excel Formulas
Excel Macros
Excel Newsletter
PRODUCTS
Up to $139.00 FREE!
Categories & SearchSearch for software
Excel Templates
Excel Add-ins
Excel Training
More....
OTHER
Excel Development


Display Excel AutoFilter Criteria


NEW! More Books..
Add to Google advanced search! Free Help!

Add Excel Answers & Search To Your Google Toolbar Details

Excel VBA: Display Excel AutoFilter Criteria. Show Auto Filter Criteria in Excel

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download Includes Advanced Excel (10 lessons) and 2 Excel VBA Courses (20 lessons).

Got any Excel Questions? Free Excel Help

Display/Show AutoFilter Criteria

Excel's AutoFilter is one of Excel's most useful features. However, one small draw-back is it's hard top tell the criteria being used at a glance. The custom Excel function below can be used to display the criteria being used for each column of the table that has AutoFilter applied. All you do is ensure you have at least 2 rows above your table, then add the custom function to each cell 2 rows above the column heading. See below;

AutoFilter Custom Function in Use

The custom function is used in each cell shown above like;

  1. =AutoFilter_Criteria(B3)
  2. =AutoFilter_Criteria(C3)
  3. =AutoFilter_Criteria(D3)

Below is the code that must be added to the Workbook, or an Excel Add-in . To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Add to Google Search Tips FREE Excel Help

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft