Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
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 :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
I saved this from a Tom Ogilvy post:
==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
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 :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
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 he 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 wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
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 :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
using auto filter, how to tell what columns are filtered?
Tom does nice work!
Robert Mark Bram wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
restrict columns that have auto filter | Excel Discussion (Misc queries) | |||
Auto filter columns while keeping groups of verticle cells togethe | Excel Discussion (Misc queries) | |||
help-sum filtered info on excel for more than one filter | Excel Worksheet Functions | |||
I need a specific value in auto filter to fill a combo box and than match the select value in 2 other columns. | Excel Worksheet Functions | |||
Suggestion to add Auto Filter option for columns | Excel Worksheet Functions |