Use VBA Function to Show Excel Filter Criteria

Have you ever received an Excel file and wondered how it was filtered without checking each column’s data filter? Or, maybe you needed to send a file and show your filters. That was a problem I faced recently when I started with a list of 3200 non-profits for the county. I needed a way for Excel to show the filters and logic. (Includes Excel practice spreadsheet with VBA code.)

Update 9/28/2020: I received an email from Robert Phillips, the author of the Excel filter function referenced below. He indicated the function has an issue with the newer structured tables, and he is working on a fix.

Normal Excel Filter View

The screen snap below shows a filtered product list from an example file. Each column heading shows the drop-down arrow on the right. In Excel, columns that have filters have the down-control arrow show as blue. If you’re not familiar with this feature, you can review an earlier Excel AutoFilter tutorial.

Excel makes it easier to identify a filtered column because it adds a small filter graphic to the drop-down arrow. If you place your mouse over the column heading, you can see the filter criteria.

excel filter criteria mouseoverPin
Excel Filter criteria show when you hover over filter

Running Into Limitations

On the first pass with my large spreadsheet, I clicked each column and wrote the column’s filter criteria. However, I soon learned I didn’t want to do that again and started looking for a better way.

Like many problems, I thought someone smarter than me had solved this issue. The first person is a Microsoft Excel MVP named Stephen Bullen. Mr. Bullen has written several books on VBA for Excel and has a website called Office Automation Ltd.

His solution was to add a user-defined function that reads the filter criteria for any cell. This allowed me to insert a function reference above each column so I could see my filter. If the column didn’t have a filter, nothing showed for that cell.

This solution was elegant but static. For example, if I wanted to change the filter to include the years 2003-2005, the top cell wouldn’t reflect this change. It could mislead the user. Fortunately, I found a workaround by Rob Markbram on his blog called Rob on Programming. He borrowed from Stephen Bullen’s code and appended the cell formula to force an update.

But as often the case with software updates, what once worked in previous versions of Excel for me broke. However, I was able to find another solution from Bob Phillips on Microsoft TechNet.

The result was I could see the filter criteria above my columns. This even worked if I used a word filter.

excel with filter criteria displayedPin
Excel with filter criteria showing above columns

Creating an Excel VBA Function to Show Filter Criteria

  1. Open the Excel spreadsheet where you would like to add this function.
  2. Press Alt + F11. The Microsoft Visual Basic Editor should appear.
  3. Highlight the entry ThisWorkbook. (Your entries will look different from mine based on your file name and Excel add-ons.)
  4. From the Insert menu, select Module.
add new excel modulePin
Adding a new module for code

You should now see a blank Code module on the right side.

blank code modulePin
New blank code module
  1. Copy Rob Phillip’s code below and paste it into the new module.
'Code from Bob Phillips @WessexBob
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sOp As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Dim i As Long

    Set sh = rng.Parent
    If sh.FilterMode = False Then
        ShowFilter = "No Active Filter"
        Exit Function
    End If
    Set frng = sh.AutoFilter.Range
    If Intersect(rng.EntireColumn, frng) Is Nothing Then
        ShowFilter = CVErr(xlErrRef)
        lngOff = rng.Column - frng.Columns(1).Column + 1
        If Not sh.AutoFilter.Filters(lngOff).On Then
            ShowFilter = "No Conditions"
            Set filt = sh.AutoFilter.Filters(lngOff)
            On Error Resume Next
            lngOp = filt.Operator
            If lngOp = xlFilterValues Then
                For i = LBound(filt.Criteria1) To UBound(filt.Criteria1)
                    sCrit1 = sCrit1 & filt.Criteria1(i) & " or "
                Next i
                sCrit1 = Left(sCrit1, Len(sCrit1) - 3)
                sCrit1 = filt.Criteria1
                sCrit2 = filt.Criteria2
                If lngOp = xlAnd Then
                    sOp = " And "
                ElseIf lngOp = xlOr Then
                    sOp = " or "
                    sOp = ""
                End If
            End If
            ShowFilter = sCrit1 & sOp & sCrit2
        End If
    End If
End Function

Notice how the new ShowFilter function shows in the top right.

Pasted VBA ShowFilter codePin
Pasted VBA ShowFilter code
  1. Click the Excel icon to return to your spreadsheet.

Inserting the ShowFilter Function

The next part is to insert the function into the cells on your worksheet. My preference is to show the filter criteria at the top, so I inserted two blank rows. I also prefer to insert the function for all the columns even if I don’t plan to filter them. You can adjust your spreadsheet accordingly.

  1. Place your cursor in cell A1.
  2. From the Formulas tab, select Insert Function
  3. In the Insert Function dialog, select the User Defined category. (Your list may look different)
insert your filter functionPin
Adding your new filter function
  1. In the Select a function: section, highlight ShowFilter.
  2. Click OK.
  3. In the Function Arguments dialog, enter the cell range for your column. In my example the range is A4:A124.
Excel cell range for functionPin
Adding in cell range for the function
  1. Click OK.

If you click cell A1, you should see your formula with the user-defined function. Because this column is not filtered, the cell displays No Active Filter.

  1. At this point, you should test the code by selecting and deselecting various items in the filter box.
excel filter criteria showsPin
Your Excel Filter criteria shows at top of column
  1. Repeat the process for any other columns.

Additional Resources

Excel macro-enabled worksheet for tutorial