This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have around 40 columns sometimes more in a workbook. Is there any shortcut or easy way to navigate to cells that are filtered. Manually scrolling to find them is not practical.
In below screenshot column D and H are filtered
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi ExcelFun,
Greetings! Thank you for posting to Microsoft Community.
There is no such way to find filter with shortcut or Goto function. I also checked it from VBA code but find nothing.
Most of this situation, I will click filter twice and it will clear all the filters.
Best Regards,
Snow Lu
I don't want to clear filter. I want to identify those columns.
I got this code from internet it works. How to quickly get rid of all the extra row data details it shows. Can you comment it out in the code.
Sub MessageFilterValues()
' 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
Dim Msg As Variant
Dim currentFiltRange 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
Change
Msg = Msg & Cells(1, f).Address(0, 0) & ": " & ValA & " " & ValB & " (" & ValC & ")" & vbLf
to
Msg = Msg & Cells(1, f).Address(0, 0) & vbLf
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
I found out the same after commenting out. Instead out directly go for msg box.
The below code is very fast if we have 50k rows.
The code uses Filter object and its Property
sht.AutoFilter.Filters.Item.Criteria1
Sub MessageFilterValuess()
Dim sht As Worksheet
Dim f As Long
Dim i As Long
Dim ItemCount As Long
Dim ItemStr As Variant
Dim Msg As Variant
Dim currentFiltRange 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
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