dynamic name range in VBA

[vinnie]

Hi,

Is this the direct code for assigning a dynamic range to the P column
staring with P2 cell? I keep getting a global name error everytime I call
this NameList range. Thanks for the help.


ActiveSheet.Names.Add Name:="NameList",
RefersTo:="=OFFSET(Sheet2!$P$2,0,0,COUNTA(Sheet2!$P:$P),1)"

-Vinnie

 

[arno]

Hi vinnie,

Is this the direct code for assigning a dynamic range to the P column
staring with P2 cell? I keep getting a global name error everytime I call
this NameList range. Thanks for the help.

maybe your column P is filled with blanks or something else. What does
the formula =COUNTA(Sheet2!$P:$P) show you when you write it in your
worksheet?

What happens if you use P1 (instead of P2):
RefersTo:="=OFFSET(Sheet2!$P$1,0,0,COUNTA(Sheet2!$P:$P),1)"


arno

 

[Peter T]

Hi Vinnie,

You are defining a Worksheet level name on the Active sheet, presumably
Sheet2

Your code would fail if you are using it whilst on another sheet, unless you
did something like this (assuming the active sheet was Sheet2):

Set rng = range("Sheet2!NameList")

Might be better to define like this:
Worksheets("Sheet2").Names.Add etc

If you had defined it as a workbook level name,

ActiveWorkbook.Names.Add etc
whatever sheet you are currently on, you would just do:
Set rng = range("MyName")

If the parent workbook is not active you would need to qualify further, both
defining and using.

Strictly speaking your name is a formula, it does not appear in the names
list left of input bar, but should still be able to use it in this context
as a range.

One more thing, code would fail if all cells in the dynamic range are
empty - nothing to count!

Regards,
Peter