This question is actually for the Excel GUI, rather than Excel "programming", per se. However, if this is unavailable in the GUI, I would be curious if there's a VBA solution (although, I have basically 0 VBA knowledge/experience).
Is there a way to see which filters are active in Excel, other than just by looking at the funnel icons? As shown in my attached screenshot, some spreadsheets can have columns that extend off the visible screen, so it can be easy to miss the funnel icon indicating an active filter. (Additionally, I think it can be pretty easy to overlook the icon, even amidst only a few columns.)
Ideally, there would be some kind of list showing which columns/headers are actively filtered.
Thanks.
If you merely want a simple list of the columns where a filter is applied then the following VBA code may suffice:
Option Explicit
Function FilterCrit() As String
Dim i As Long
Dim ws As Worksheet
Dim Filter As String
'Application.Volatile
Set ws = ThisWorkbook.Worksheets(1)
If Not ws.FilterMode Then
FilterCrit = "not filtered"
Exit Function
End If
For i = 1 To ws.AutoFilter.Filters.Count
If ws.AutoFilter.Filters(i).On Then
FilterCrit = FilterCrit & "Filter on column " & i & Chr(10)
End If
Next i
End Function
This will iterate the columns and if a filter is applied on any of these columns then it will be listed.
By default all UDFs user defined functions are not volatile and therefore do not automatically recalculate. Yet, you can force them to automatically recalculate with Application.Volatile. But it is highly recommend not to use this option as it can severely slow down your Excel file. Alternative solutions are recommended here: Alternative to Application.Volatile to auto update UDF
This will work to highlight columns which contain active filter. This code just Bolds and sets the font color Red, but you can modify the style changes as per your need.
Sub test()
Call markFilter(ActiveSheet)
End Sub
Sub markFilter(wks As Worksheet)
Dim lFilCol As Long
With wks
If .AutoFilterMode Then
For lFilCol = 1 To .AutoFilter.Filters.Count
'/ If filter is applied then mark the header as bold and font color as red
If .AutoFilter.Filters(lFilCol).On Then
.AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbRed
.AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = True
Else
'/ No Filter. Column header font normal and black.
.AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbBlack
.AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = False
End If
Next
Else
'/ No Filter at all. Column header font normal and black.
.UsedRange.Rows(1).Font.Color = vbBlack
.UsedRange.Rows(1).Font.Bold = False
End If
End With
End Sub
Example
Sub Active_Filter()
Dim Sht As Worksheet
Dim lngCount As Long
Dim i As Long
Set Sht = ActiveSheet
If Sht.FilterMode Then
lngCount = Sht.AutoFilter.Filters.Count
' // Go through each column look for active Filter
For i = 1 To lngCount Step 1
If Sht.AutoFilter.Filters(i).On Then
Debug.Print "Filter is set on " & Sht.Columns(i).Address
End If
Next i
End If
End Sub