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

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

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 could use a DYNAMIC range name

in teh Insert Names window, you set the refers to box like this

=OFFSET(Sheet1!$D$9,0,0,COUNTA(Sheet1!$D:$D),1)

here, my list starts at D9
I have no other data in D so its safe to count any item as it will be in the
list
if you're sure that the list will never do beyond a certain size, you could
limit the length..
=OFFSET(Sheet1!$D$9,0,0,COUNTA(Sheet1!$D9:$D19),1)

this says from D9, oddet no rows or columns, but make it several rows and
one column




"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

PERFECT! Thank you SO much.

cm


"Patrick Molloy" wrote:

you could use a DYNAMIC range name

in teh Insert Names window, you set the refers to box like this

=OFFSET(Sheet1!$D$9,0,0,COUNTA(Sheet1!$D:$D),1)

here, my list starts at D9
I have no other data in D so its safe to count any item as it will be in the
list
if you're sure that the list will never do beyond a certain size, you could
limit the length..
=OFFSET(Sheet1!$D$9,0,0,COUNTA(Sheet1!$D9:$D19),1)

this says from D9, oddet no rows or columns, but make it several rows and
one column




"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

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