I was asked by a colleague if there was a way to discover what filters were on so I came up with this bit of code to flash the filtered columns:
Sub huntTheFilter2()
On Error GoTo Jumpout
FiltCols = 0
For Each filt In Worksheets(ActiveSheet.Name).AutoFilter.Filters
If (filt.On) Then
If filt.Parent.Range.Column + FiltCols < 26 Then
ColumnLetter = Chr(filt.Parent.Range.Column + FiltCols + 64)
Else
ColumnLetter = Chr(Int((filt.Parent.Range.Column + FiltCols - 1) / 26) + 64) & Chr(((filt.Parent.Range.Column + FiltCols - 1) Mod 26) + 65)
End If
filtCrit1 = filt.Criteria1
filtCrit2 = ""
If filt.Operator = xlAnd Then
filtCrit2 = " AND " & filt.Criteria2
ElseIf filt.Operator = xlOr Then
filtCrit2 = " OR " & filt.Criteria2
End If
For x1 = 1 To 5
Range(ColumnLetter & ":" & ColumnLetter).Select
For x2 = 1 To 12345678
Next
Range(ColumnLetter & "1").Select
For x2 = 1 To 12345678
Next
Next
'MsgBox ("Filter in column " & ColumnLetter & " set to: " & filtCrit1 & filtCrit2)
End If
FiltCols = FiltCols + 1
Next
Jumpout:
End Sub
un-REM the MsgBox line if you want the results displayed in a series of message boxes too.
The difference between the blue and the black of the inverted filter triangles is not always apparant and I've found myself using this almost daily - can't think whey I never got around to writing something before.
Sub huntTheFilter2()
On Error GoTo Jumpout
FiltCols = 0
For Each filt In Worksheets(ActiveSheet.Name).AutoFilter.Filters
If (filt.On) Then
If filt.Parent.Range.Column + FiltCols < 26 Then
ColumnLetter = Chr(filt.Parent.Range.Column + FiltCols + 64)
Else
ColumnLetter = Chr(Int((filt.Parent.Range.Column + FiltCols - 1) / 26) + 64) & Chr(((filt.Parent.Range.Column + FiltCols - 1) Mod 26) + 65)
End If
filtCrit1 = filt.Criteria1
filtCrit2 = ""
If filt.Operator = xlAnd Then
filtCrit2 = " AND " & filt.Criteria2
ElseIf filt.Operator = xlOr Then
filtCrit2 = " OR " & filt.Criteria2
End If
For x1 = 1 To 5
Range(ColumnLetter & ":" & ColumnLetter).Select
For x2 = 1 To 12345678
Next
Range(ColumnLetter & "1").Select
For x2 = 1 To 12345678
Next
Next
'MsgBox ("Filter in column " & ColumnLetter & " set to: " & filtCrit1 & filtCrit2)
End If
FiltCols = FiltCols + 1
Next
Jumpout:
End Sub
un-REM the MsgBox line if you want the results displayed in a series of message boxes too.
The difference between the blue and the black of the inverted filter triangles is not always apparant and I've found myself using this almost daily - can't think whey I never got around to writing something before.