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
Bookmarks