using auto filter, how to tell what columns are filtered?

[Robert Mark Bram]

Hi all,

I know that when using auto-filter, the little arrow on the top of a
column goes blue to indicate you are filtering by that column. But I
have a few spreadsheets with 20 or more columns that I am using
filtering on, so the colour change is not obvious. Is there a more
obvious way to illuminate filtered columns?

Thanks for any assistance!

Rob

 

[Dave Peterson]

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm
or
http://spreadsheetpage.com/index.php/tip/displaying_autofilter_criteria/

to get it to refresh:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

this is one I wrote back in 2000

Here is a user defined function that will display the criteria in a cell:

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
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)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function

=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

would show the filter for column 2

I usually put these functions in cells above the filter

==============
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Robert Mark Bram]

Hi Dave,

Thank you very much for your response!

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm
orhttp://spreadsheetpage.com/index.php/tip/displaying_autofilter_criteria/

Following http://j-walk.com/ss/excel/usertips/tip044.htm, here is what
I did:

1) Added a new row at top of my spreadsheet, so the filters are at row
2.
2) Pressed Alt+11 to get to Microsoft Visual Basic.
3) Double clicked on ThisWorkbook.
4) Pasted in
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
5) Saved and closed VB.
6) In the cell above G2 (which has a filter), inserted:
=FilterCriteria(G2)
7) I get #NAME? error in G1.

Any ideas?

Thanks again.

Rob

 

[Dave Peterson]

The code doesn't go in the ThisWorkbook module. It belongs in a General module.

Some more links:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

 

[Robert Mark Bram]

Thanks Dave,

Once I worked out the "general module" bit, I got your version working
nicely.
=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

Well Done!

Rob

 

[Dave Peterson]

Tom does nice work!

Thanks Dave,

Once I worked out the "general module" bit, I got your version working
nicely.
=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

Well Done!

Rob