MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Which Excel Columns are Filtered?


January 28, 2020 - by Bill Jelen

Karen from Chicago has a worksheet with 200 columns. The filter drop-downs are enabled. She wants a quick way to see which columns have a filter applied and what filter is applied to those columns.

Karen already knew to look for the Funnel icon in the header row, but with hundreds of columns, this could be time-consuming.

My first stop was UserVoice. There was an idea posted there, but it only has one vote.

I asked my MVP friends if they had any thoughts on how to do this.

Roger Govier had some code that he originally wrote for an article on Contextures. He adapted that code and then I adapted his code using the great list provided at https://yoursumbuddy.com/autofilter-vba-operator-parameters/.

Roger’s code loops through all of the columns in the current sheet’s AutoFilter. If a filter is set, it looks at the ActiveSheet.AutoFilter.Filters.Item(1).Criteria1, .Criteria2, and .Operator to figure out what filter is applied and what was selected for the column.


If you open an AutoFilter dropdown and choose Andy, Betty, Charlie, then .Criteria1 is going to be an array of items. Roger checks for an array by asking for the Upper Bound of the array with UBound(.Criteria1). If there is an array, he loops through the items in the array. If there is not an array, he can simply use .Criteria1.

There is a .Criteria2 property, but it only seems to be used if the filter type is OR.

The .Operator property has a series of numeric codes:

  • 0 for Single Item
  • 1 for AND
  • 2 for OR
  • 3 for Top 10
  • 4 for Bottom 10
  • 5 for Top 10 Percent
  • 6 for Bottom 10 Percent
  • 7 for Filter by Values
  • 8 for Cell Color
  • 9 for Font Color
  • 10 for Icon
  • 11 for Dynamic

If the .Operator is 11, then there are 34 possible codes stored in .Criteria1. See the VBA code below, but it is 1 for Today, 2 for Yesterday, 3 for Tomorrow, and so on.

The first code gives Karen a listing of all the filters and how they are applied:

Filtered Columns
Filtered Columns

Roger Govier realized this would be hard to use. He suggested inserting three blank rows above your data. Run the ShowFilterValues code and you can quickly jump to the next filtered column by using Ctrl + Shift + RightArrow in row 1.

Result
Result

Watch Video

Download Excel File

You can download the excel file: which-excel-columns-are-filtered.xlsm

Or you can copy and paste either macro from below.

Sub MessageFilterValues()
    ' Thanks to Excel MVP Roger Govier for original code
    ' Modified by Bill Jelen on 12 May 2019
    ' based on https://yoursumbuddy.com/autofilter-vba-operator-parameters/
    ' This assumes your headings are in row 1.
    Dim sht As Worksheet
      
    Dim f As Long
    Dim i As Long
    Dim ItemCount As Long
    Dim ItemStr As Variant
    Dim ValA As Variant
    Dim ValB As Variant
    Dim ValC As Variant
    Dim ValD As Variant

    Set sht = ActiveSheet
    Msg = ""
    
    sht.[A1].Select
    With sht.AutoFilter
        currentFiltRange = .Range.Address
        With .Filters
            For f = 1 To .Count
                With .Item(f)
                    If .On Then
                        ValA = ""
                        ValB = ""
                        ValC = ""
                        ValD = ""
                        ' Is .Criteria1 an array?
                        Err.Clear
                        On Error Resume Next
                        ItemCount = UBound(.Criteria1)
                        If Err.Number = 0 Then
                            ItemStr = ""
                            For i = 1 To ItemCount
                                ItemStr = ItemStr & .Criteria1(i)
                            Next i
                            ValA = ItemStr
                        Else
                            ' Not an array
                            ValA = .Criteria1
                        End If
                        On Error Resume Next
                        ' .Criteria2 is only used for XLOr
                        ValB = .Criteria2
                        On Error GoTo 0
                        ' Operator is a series of codes
                        Select Case .Operator
                            Case 0
                                ValC = "Single Item"
                            Case 1
                                ValC = "xlAnd"
                            Case 2
                                ValC = "xlOr"
                            Case 3
                                ValC = "xlTop10Items"
                            Case 4
                                ValC = "xlBottom10Items"
                            Case 5
                                ValC = "xlTop10Percent"
                            Case 6
                                ValC = "xlBottom10Percent"
                            Case 7
                                ValC = "xlFilterValues"
                            Case 8
                                ValC = "xlFilterCellColor"
                            Case 9
                                ValC = "xlFilterFontColor"
                            Case 10
                                ValC = "xlFilterIcon"
                                ValA = "Icon #" & .Criteria1.Index
                            Case 11
                                ValC = "xlFilterDynamic"
                                ' For Dynamic, there are one of 34 values stored in Criteria1
                                ' Update Criteria1 stored in row 1
                                Select Case ValA
                                    Case 1
                                        ValD = "Today"
                                    Case 2
                                        ValD = "Yesterday"
                                    Case 3
                                        ValD = "Tomorrow"
                                    Case 4
                                        ValD = "This Week"
                                    Case 5
                                        ValD = "Last Week"
                                    Case 6
                                        ValD = "Next Week"
                                    Case 7
                                        ValD = "This Month"
                                    Case 8
                                        ValD = "Last Month"
                                    Case 9
                                        ValD = "Next Month"
                                    Case 10
                                        ValD = "This Quarter"
                                    Case 11
                                        ValD = "Last Quarter"
                                    Case 12
                                        ValD = "Next Quarter"
                                    Case 13
                                        ValD = "This Year"
                                    Case 14
                                        ValD = "Last Year"
                                    Case 15
                                        ValD = "Next Year"
                                    Case 16
                                        ValD = "Year to Date"
                                    Case 17
                                        ValD = "Q1"
                                    Case 18
                                        ValD = "Q2"
                                    Case 19
                                        ValD = "Q3"
                                    Case 20
                                        ValD = "Q4"
                                    Case 21
                                        ValD = "January"
                                    Case 22
                                        ValD = "February"
                                    Case 23
                                        ValD = "March"
                                    Case 24
                                        ValD = "April"
                                    Case 25
                                        ValD = "May"
                                    Case 26
                                        ValD = "June"
                                    Case 27
                                        ValD = "July"
                                    Case 28
                                        ValD = "August"
                                    Case 29
                                        ValD = "September"
                                    Case 30
                                        ValD = "October"
                                    Case 31
                                        ValD = "November"
                                    Case 32
                                        ValD = "December"
                                    Case 33
                                        ValD = "Above Average"
                                    Case 34
                                        ValD = "Below Average"
                                End Select
                                ValA = ValD
                        End Select
                        Msg = Msg & Cells(1, f).Address(0, 0) & ": " & ValA & " " & ValB & " (" & ValC & ")" & vbLf
                    End If
                End With
            Next f
        End With
    End With
    If Msg = "" Then Msg = "No columns filtered"
    MsgBox Prompt:=Msg, Title:="Filtered Columns"
End Sub


Sub ShowFilterValues()
    ' Thanks to Excel MVP Roger Govier for original code
    ' Modified by Bill Jelen on 12 May 2019
    ' based on https://yoursumbuddy.com/autofilter-vba-operator-parameters/
    ' Requires you to have three blank rows above your data
    Dim sht As Worksheet
    Dim filterArray()
      
    Dim f As Long
    Dim i As Long
    Dim ItemCount As Long
    Dim ItemStr As Variant

    Set sht = ActiveSheet
    sht.Rows("1:3").ClearContents
    With sht.Rows("1:3")
        .ClearContents
        .NumberFormat = "@"
        With .Font
            .Bold = True
            .Color = XlRgbColor.rgbRed
        End With
    End With
    
    sht.[A4].Select
    With sht.AutoFilter
        currentFiltRange = .Range.Address
        With .Filters
            ReDim filterArray(1 To .Count)
            For f = 1 To .Count
                With .Item(f)
                    If .On Then
                        ' Is .Criteria1 an array?
                        Err.Clear
                        On Error Resume Next
                        ItemCount = UBound(.Criteria1)
                        If Err.Number = 0 Then
                            ItemStr = ""
                            For i = 1 To ItemCount
                                ItemStr = ItemStr & .Criteria1(i)
                            Next i
                            sht.Cells(1, f) = ItemStr
                        Else
                            ' Not an array
                            sht.Cells(1, f) = .Criteria1
                        End If
                        On Error Resume Next
                        ' .Criteria2 is only used for XLOr
                        sht.Cells(2, f) = .Criteria2
                        On Error GoTo 0
                        ' Operator is a series of codes
                        Select Case .Operator
                            Case 0
                                sht.Cells(3, f) = "Single Item"
                            Case 1
                                sht.Cells(3, f) = "xlAnd"
                            Case 2
                                sht.Cells(3, f) = "xlOr"
                            Case 3
                                sht.Cells(3, f) = "xlTop10Items"
                            Case 4
                                sht.Cells(3, f) = "xlBottom10Items"
                            Case 5
                                sht.Cells(3, f) = "xlTop10Percent"
                            Case 6
                                sht.Cells(3, f) = "xlBottom1010Percent"
                            Case 7
                                sht.Cells(3, f) = "xlFilterValues"
                            Case 8
                                sht.Cells(3, f) = "xlFilterCellColor"
                            Case 9
                                sht.Cells(3, f) = "xlFilterFontColor"
                            Case 10
                                sht.Cells(3, f) = "xlFilterIcon"
                                sht.Cells(1, f) = "Icon #" & .Criteria1.Index
                            Case 11
                                sht.Cells(3, f) = "xlFilterDynamic"
                                ' For Dynamic, there are one of 34 values stored in Criteria1
                                ' Update Criteria1 stored in row 1
                                Select Case sht.Cells(1, f).Value
                                    Case 1
                                        sht.Cells(1, f).Value = "Today"
                                    Case 2
                                        sht.Cells(1, f).Value = "Yesterday"
                                    Case 3
                                        sht.Cells(1, f).Value = "Tomorrow"
                                    Case 4
                                        sht.Cells(1, f).Value = "This Week"
                                    Case 5
                                        sht.Cells(1, f).Value = "Last Week"
                                    Case 6
                                        sht.Cells(1, f).Value = "Next Week"
                                    Case 7
                                        sht.Cells(1, f).Value = "This Month"
                                    Case 8
                                        sht.Cells(1, f).Value = "Last Month"
                                    Case 9
                                        sht.Cells(1, f).Value = "Next Month"
                                    Case 10
                                        sht.Cells(1, f).Value = "This Quarter"
                                    Case 11
                                        sht.Cells(1, f).Value = "Last Quarter"
                                    Case 12
                                        sht.Cells(1, f).Value = "Next Quarter"
                                    Case 13
                                        sht.Cells(1, f).Value = "This Year"
                                    Case 14
                                        sht.Cells(1, f).Value = "Last Year"
                                    Case 15
                                        sht.Cells(1, f).Value = "Next Year"
                                    Case 16
                                        sht.Cells(1, f).Value = "Year to Date"
                                    Case 17
                                        sht.Cells(1, f).Value = "Q1"
                                    Case 18
                                        sht.Cells(1, f).Value = "Q2"
                                    Case 19
                                        sht.Cells(1, f).Value = "Q3"
                                    Case 20
                                        sht.Cells(1, f).Value = "Q4"
                                    Case 21
                                        sht.Cells(1, f).Value = "January"
                                    Case 22
                                        sht.Cells(1, f).Value = "February"
                                    Case 23
                                        sht.Cells(1, f).Value = "March"
                                    Case 24
                                        sht.Cells(1, f).Value = "April"
                                    Case 25
                                        sht.Cells(1, f).Value = "May"
                                    Case 26
                                        sht.Cells(1, f).Value = "June"
                                    Case 27
                                        sht.Cells(1, f).Value = "July"
                                    Case 28
                                        sht.Cells(1, f).Value = "August"
                                    Case 29
                                        sht.Cells(1, f).Value = "September"
                                    Case 30
                                        sht.Cells(1, f).Value = "October"
                                    Case 31
                                        sht.Cells(1, f).Value = "November"
                                    Case 32
                                        sht.Cells(1, f).Value = "December"
                                    Case 33
                                        sht.Cells(1, f).Value = "Above Average"
                                    Case 34
                                        sht.Cells(1, f).Value = "Below Average"
                                End Select
                        End Select
                    End If
                End With
            Next f
        End With
    End With
End Sub

Excel MVP Jon Acampora offers a $27 Filter-Mate add-in with similar functionality. Learn more at https://www.excelcampus.com/filter-mate/.

Title Photo: Marvin Ronsdorf on Unsplash


Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.