View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Jako[_8_] Jako[_8_] is offline
external usenet poster
 
Posts: 1
Default Naming a range with VBA...

Stef here is a reply i got many months ago from an MVP on this forum.
The code was NOT written by me.


Try something like the following:

If being done from VBA in Excel

Workbooks("Book1.xls").Names.Add Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"

If being done from VBA in any application other than Excel

<ApplicationObject.Workbooks("Book1.xls").Names.A dd Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"

Where <ApplicationObject is the variable that refers to the Excel
Application instance

Easier would be:

rnum = 21
With xlwksht2
.Range("A3:A" & rnum).Name = Name1
End With

Hope this is of help

--
Message posted from http://www.ExcelForum.com