Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering
Need a macro for Excel 2007
A very large spreadsheet has 89 columns (I know - this should be in Access), and filtering is used to analyze. Sometimes users will filter once, then filter a second or third time, and get lost as to which columns they filtered on. Is there a way to know which column is the last filter? Would a solution be to change font color of the header cell of the last column filtered - to know how to retrace and undo their filters? What would the code be and where would it be put - in the workbook/worksheet or in "Personal?" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering
first, The pulldown box has a small tab that is colored when the Filter is
applied. I know it is hard to see but it is there. If you are using autofilter the best thing is to turn off all the filters and then turn then back on using the menu. You could keep a history of the applied filters on a worksheet. I would use a worksheet change function to keep track of the order the filters were applied. The write an undo filter to remove the filters. I would use the workbook open function to store each of the filter settting for all 89 columns on a special worksheet. Store the Column Number in Column A and the Value of each settting in column B with each column opn a diffferent row. There will be 89 rows after you open the workbook. Every Time a filter is apllied add a row to the special worksheet for the new setting. Then write an UNDO macro which when call will remove the last Row in the special worksheet and then search up (from lastrow to first row) the history list to find the last value that the column was set to. "Phil H." wrote: Need a macro for Excel 2007 A very large spreadsheet has 89 columns (I know - this should be in Access), and filtering is used to analyze. Sometimes users will filter once, then filter a second or third time, and get lost as to which columns they filtered on. Is there a way to know which column is the last filter? Would a solution be to change font color of the header cell of the last column filtered - to know how to retrace and undo their filters? What would the code be and where would it be put - in the workbook/worksheet or in "Personal?" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering
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 ==================== You may want to put the formula above the cell with the autofilter arrow. =========== If you want to use this with lots of workbooks, then you could put it in your personal.xls workbook. You'd use it like this: =personal.xlsm!ShowFilter(B5)&left(Subtotal(9,B5:B 200),0) If you want to share the single workbook with others, then you'd want to put it into the workbook. But if you want to use it with lots of workbooks and share with others, then I'd suggest that you put it in a separate workbook (PhilsUtils.xlsm) and share that with others. Tell them to save your workbook in a folder on their C: drive. And everyone should use the same name: C:\xlUtils\philsutils.xlsm (You could also distribute an addin (.xlam) and have them store it in the same location, but give them instructions on how to install the addin.) Phil H. wrote: Need a macro for Excel 2007 A very large spreadsheet has 89 columns (I know - this should be in Access), and filtering is used to analyze. Sometimes users will filter once, then filter a second or third time, and get lost as to which columns they filtered on. Is there a way to know which column is the last filter? Would a solution be to change font color of the header cell of the last column filtered - to know how to retrace and undo their filters? What would the code be and where would it be put - in the workbook/worksheet or in "Personal?" -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering
Thanks Joel and Dave. Need to study this more - your insight is very helpful.
"Dave Peterson" wrote: 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 ==================== You may want to put the formula above the cell with the autofilter arrow. =========== If you want to use this with lots of workbooks, then you could put it in your personal.xls workbook. You'd use it like this: =personal.xlsm!ShowFilter(B5)&left(Subtotal(9,B5:B 200),0) If you want to share the single workbook with others, then you'd want to put it into the workbook. But if you want to use it with lots of workbooks and share with others, then I'd suggest that you put it in a separate workbook (PhilsUtils.xlsm) and share that with others. Tell them to save your workbook in a folder on their C: drive. And everyone should use the same name: C:\xlUtils\philsutils.xlsm (You could also distribute an addin (.xlam) and have them store it in the same location, but give them instructions on how to install the addin.) Phil H. wrote: Need a macro for Excel 2007 A very large spreadsheet has 89 columns (I know - this should be in Access), and filtering is used to analyze. Sometimes users will filter once, then filter a second or third time, and get lost as to which columns they filtered on. Is there a way to know which column is the last filter? Would a solution be to change font color of the header cell of the last column filtered - to know how to retrace and undo their filters? What would the code be and where would it be put - in the workbook/worksheet or in "Personal?" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering | Excel Discussion (Misc queries) | |||
Filtering | Excel Discussion (Misc queries) | |||
filtering | Excel Discussion (Misc queries) | |||
filtering | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |