Range Names

Topic

New Reply

How can I create a Worksheet Level Range Name in Excel in VBA? In Excel itself you need to add ‘!’ before the range name, such as Name: Data RefersTo: =!A1:D4. How can I do this in programming? Cheers, Andy.

Reply | Quote

Replies

Try something like :

	ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Sheet1!A1:D4"

which should name the Range A1:D4 as Test.

Hope that is of assistance

Andrew

Reply | Quote

Thank you for your prompt reply.
However, I wish to create a range name that doesn’t refer to a specific sheet. In Excel you can use RefersTo: =!$A$1:$D$4 (without a sheet reference). Typing the range name in the name box and pressing Enter selects A1:D4 for whichever sheet you happen to be in. However, trying to do the same in VBA generates an error.

Reply | Quote

Sorry about that, but try the following :

	    ActiveWorkbook.Names.Add Name:="TEST", RefersToR1C1:="=!R1C1:R4C4"

I think that might give you what you want

Andrew

Reply | Quote