Filtering

[Phil H.]

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?"

 

[Joel]

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.

 

[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
====================

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:B200),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.]

Thanks Joel and Dave. Need to study this more - your insight is very helpful.