Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic name range in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic name range in VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic name range in VBA
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 "vinnie" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic range name | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming | |||
Dynamic Range | Excel Programming | |||
Sum Up Dynamic Range | Excel Programming |