VBA code to locate cell address of AutoFilter dropdown box

[Guest]

XL 2003

What VBA code line would return the cell address of the cell containing the
AutoFilter dropdown box in a particular column

The line below does not work but gives an idea what I am looking for.

ActiveSheet.Autofilter.Address

Dennis

 

[Bernie Deitrick]

Dennis,

Try the code below.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim i As Integer
Dim mySheet As Worksheet
Set mySheet = ActiveSheet
Dim myFilters As String

myFilters = ""

For i = 1 To mySheet.AutoFilter.Range.Columns.Count
If mySheet.AutoFilter.Filters(i).On Then
myFilters = myFilters & mySheet.AutoFilter.Range _
.Cells(1, i).Address(False, False) & " & "
End If
Next i

MsgBox mySheet.Name & " range " & _
mySheet.AutoFilter.Range.Address & Chr(10) & _
"is filtered by cell(s) " & _
Left(myFilters, Len(myFilters) - 3)

End Sub

 

[Earl Kiosterud]

Dennis

ActiveSheet.AutoFilter.Range.Row gives you the number of the heading row,
which contains the dropdowns.

The following will select the first one.
Roww = ActiveSheet.AutoFilter.Range.Row
Coll = ActiveSheet.AutoFilter.Range.Column
Cells(Roww, Coll).Select

 

[Guest]

Thanks to both of you!

Different approaches both very useful.

I was close, but VBA likes neither horseshoes nor hand-grenades.

Dennis

*****************************************