need macro for auto range name (#rows different each time)
On Thursday, September 17, 2009 at 10:43:01 AM UTC-5, Jacob Skaria wrote:
Try the below. The macro will identify the last filled row in Column A and
create a named range...
Dim lngLastRow As Long
lngLastRow = Worksheets("Sheet1").Cells(Rows.Count, _
"A").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="newRange", _
RefersTo:="=Sheet1!$A$1:$A$" & lngLastRow
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"cm" wrote:
I need a macro to name a range that will have a different number of populated
rows each time the macro is generated. Recording the keystrokes is not
successful.
I can set the range name to include enough rows to accommodate the most
possible records, however this range is being used for a data validation
drop-down list. The result with a pre-set range name produces too many blank
rows at the bottom of the list -- looks unprofessional.
Please advise,
cm
you are my hero! worked perfectly!!!
|