Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter.Filters--What column is being referenced when On?
Regarding the suggestion by Tom Ogilvy below, let's say that I have ten
columns with filters but only two of them are On, with criteria. I can loop through the filters to see which ones are on and get the criteria, but how do I determine which column it is that has the filter on. Your code returns the first column in the range being filtered but doesn't help identify where the filters are that are on. So my question remains, how can a return the column number or address of the column being filtered "Tom Ogilvy" wrote in message ... activesheet.Autofilter.Range.columns(1).Column the filter count doesn't change. -- Regards, Tom Ogilvy "BTuohy" wrote: How can one discern what column is being referenced when a filter is on? For example, ActiveSheet.AutoFilter.Filters(1) might refer to what COLUMN or RANGE when .On is true? Thanks for your help. BT in MN |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter.Filters--What column is being referenced when On?
John Walkenbach has a routine by Stephen Bullen that returns the filter criteria
for any column. http://j-walk.com/ss/excel/usertips/tip044.htm Option Explicit Sub testme() Dim iCtr As Long Dim wks As Worksheet Set wks = ActiveSheet With wks.AutoFilter For iCtr = 1 To .Range.Columns.Count If .Filters(iCtr).On Then MsgBox .Range.Columns(iCtr).Cells(1).Address(0, 0) _ & " Has filters on" End If Next iCtr End With End Sub You could use Stephen's code, too: Option Explicit Sub testme() Dim myCell As Range Dim wks As Worksheet Dim myStr As String Set wks = ActiveSheet For Each myCell In wks.AutoFilter.Range.Rows(1).Cells myStr = "" myStr = FilterCriteria(myCell) If myStr = "" Then 'do nothing, no filter in use Else MsgBox myCell.Address(0, 0) & vbLf & myStr End If Next myCell End Sub 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 BTuohy wrote: Regarding the suggestion by Tom Ogilvy below, let's say that I have ten columns with filters but only two of them are On, with criteria. I can loop through the filters to see which ones are on and get the criteria, but how do I determine which column it is that has the filter on. Your code returns the first column in the range being filtered but doesn't help identify where the filters are that are on. So my question remains, how can a return the column number or address of the column being filtered "Tom Ogilvy" wrote in message ... activesheet.Autofilter.Range.columns(1).Column the filter count doesn't change. -- Regards, Tom Ogilvy "BTuohy" wrote: How can one discern what column is being referenced when a filter is on? For example, ActiveSheet.AutoFilter.Filters(1) might refer to what COLUMN or RANGE when .On is true? Thanks for your help. BT in MN -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter.Filters--What column is being referenced when On? | Excel Programming | |||
Keep function result after removing referenced column | Excel Worksheet Functions | |||
How do I update filters when using Autofilter? | Excel Worksheet Functions | |||
AutoFilter on one sheet filters same on other sheets? | Excel Programming | |||
Turn ON Autofilter Filters | Excel Programming |