Add selection to named range using VBA?

Question

I have a named range that is called tempPrintArea and refers to

='Label packinglist'!$A$1:$J$59

I want to use VBA to add another selection to it, so that it refers to

='Label packinglist'!$A$1:$J$59,'Label packinglist'!$A$61:$J$110

How can I do this?

I'm envisioning something like

Range("tempPrintArea").RefersTo = wks.Range("tempPrintArea").Address & wks.Range("$A$61:$J$110")

... but that doesn't work

do you want to add reference to exiting named range or create new named range? — Dmitry Pavliv
– Dmitry Pavliv, Commented Apr 11, 2014 at 12:57
I want to add the reference to the existing named range called tempPrintArea — user1283776
– user1283776, Commented Apr 11, 2014 at 12:58

Dmitry Pavliv · Accepted Answer · 2014-04-11 13:01:32Z

4

Suppose that you've created named range like this:

Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Label packinglist")

wks.Names.Add Name:="tempPrintArea", RefersTo:=wks.Range("A1:J59")

next step is to add new reference to it:

wks.Names("tempPrintArea").RefersTo = Union(wks.Range("tempPrintArea"), wks.Range("A61:J110"))

and now

Dim test As String
test = wks.Range("tempPrintArea").Address ' returns $A$1:$J$59,$A$61:$J$110

enter image description here

answered Apr 11, 2014 at 13:01

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

No need for two steps. The second step will overwrite any existing name, so it is all you need.

@ExcelDevelopers, I know, first part follows from another OP's question: stackoverflow.com/questions/23012097/… and I've decided to leave it