ExcelBanter - 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