Display Excel AutoFilter Criteria
Category: [Excel] Demo Available
Display Excel AutoFilter Criteria
Excel VBA: Display Excel AutoFilter Criteria. Show Auto Filter Criteria in Excel
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:
The custom function is used in each cell shown above like;
- =AutoFilter_Criteria(B3)
- =AutoFilter_Criteria(C3)
- =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
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
See also:
| Automatically Add Date, Time or Date & Time |
| Use AutoFilter in Excel VBA to Filter by Date & Time |
| Excel AutoFilters in VBA using Dates |
| Criteria for VBA filters |
| Excel VBA AutoFilters |
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.