need macro for auto range name (#rows different each time)
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
|