Programmatically defining dynamic range

[Guest]

For awhile now, I have been using code which creates a set of Range names
from a table list which provides individual range names along with the Row
and Cell numbers to be used in the refers to assignment statements. However,
as I add new named ranges to the list I have to manually change my code which
creates/recreates the named range that refers to that list to account for the
new items. I have run accross the =OFFSET Method which also employs the
CountA function to create a dynamically sizable range. However, I can only
get that to work by manually creating the range name through the insert/name
menu option.

The following is the code I'm trying to use. It runs, but without creating
the range name. It does not throw an error. Also, I use R1C1 notation. Any
advice on this would be appreciated.

ThisWorkbook.Names.Add Name:="Setup" & "!" & _
"StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _
",0,0, CountA(" & "Setup" & "!R3C30:RC30),5)", Visible:=True

Thank you,
Doug

 

[Guest]

Dynamic named ranges do not show up in the Name Box on the formula bar, so
you may find that your code is created the named range without you knowing it
- which is why it is not throwing an error.

Secondly you may want to include a row component to your range being counted
otherwise it the range will be relative to the active cell. Try:

ThisWorkbook.Names.Add Name:="Setup" & "!" & _
"StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _
",0,0, CountA(" & "Setup" & "!R3C30:R100C30),5)", Visible:=True

Then type StaticRanges in the Name Box to check the result.

Hope this helps
Rowan

 

[Guest]

Thank you, I didn't realize that they wouldn't show up in the names box, even
if the visible property has been set to true, so I assumed it wasn't created.

 

[Guest]

You're welcome.

Thank you, I didn't realize that they wouldn't show up in the names box, even
if the visible property has been set to true, so I assumed it wasn't created.