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 mouseover
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 displayed
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.
  5. add new excel module
    Adding a new module for code

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

    blank code module
    New blank code module
  6. Copy Rob Phillip’s code below and paste it into the new module.
  7. '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 code
    Pasted VBA ShowFilter code
  8. 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)
  4. insert your filter function
    Adding your new filter function
  5. In the Select a function: section, highlight ShowFilter.
  6. Click OK.
  7. In the Function Arguments dialog, enter the cell range for your column. In my example the range is A4:A124.
  8. Excel cell range for function
    Adding in cell range for the function
  9. Click OK.
  10. 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.

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

Additional Resources

Excel macro-enabled worksheet for tutorial