Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel - UDF
This is a great function to use if you often filter data, especially if you then disseminate the filtered results to others who might want to know what you used to filter the data. This is also easy to use since you only have to input the cell reference of the header to the filtered data in order to return the criteria for the filter. It is probably best to put this function in a cell directly above the filter header so that it is easy to locate and understand.
Where to install the macro: Module
UDF to Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel
Function AUTOFILTERSHOWCRITERIA(Header As Range) As String
Dim strCri1 As String
Dim 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
Subscribe for Weekly Tutorials
Helpful tutorials delivered to your email!
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.