Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA code to locate cell address of AutoFilter dropdown box
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 |
#2
|
|||
|
|||
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 "Dennis" wrote in message ... 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 |
#3
|
|||
|
|||
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 -- Earl Kiosterud www.smokeylake.com "Dennis" wrote in message ... 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 |
#4
|
|||
|
|||
Thanks to both of you!
Different approaches both very useful. I was close, but VBA likes neither horseshoes nor hand-grenades. Dennis ***************************************** "Dennis" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Find the cell value in excel by using vb code | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
in excel I want to set up a drop down & link that to another cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |