Hi,

Find the last used row and then set the range

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
ActiveSheet.Names.Add Name:="MyRange", RefersTo:=ActiveSheet.Range("A1:A" &
lastrow)

Mike

"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