updating a named range

[xzaqus]

Hello,

I have a named range (rangeAdded) that is created with an inputbox
rng = Application.InputBox("Select a range", "Obtain Range", Type:=8)
ThisWorkbook.Names.Add Name:="rangeAdded", RefersTo:=rng

For example, rangeAdded = Sheet1!$D$3:$F$5



Next, I use the inputbox again and select a range. Now, I need this to be added to the existing range so it includes both the old and the new range. If I now select Sheet1!$F$2:$H$5, the final range stored should become

rangeAdded = Sheet1!$D$3:$F$5,Sheet1!$F$2:$H$5

I tried union and simple &, but could not get it to work. Any help will be appreciated.

 

[rlv01]

Sub NamedRangeModify()
    Dim R1 As Range
    Dim N As Name

    ThisWorkbook.Names.Add "rangeAdded", "=Sheet1!$D$3:$F$5"
    Set N = Names("rangeAdded")
    Debug.Print N.RefersTo
    
    Set R1 = Application.Union(N.RefersToRange, ThisWorkbook.Worksheets("Sheet1").Range("$F$2:$H$5"))
    Debug.Print R1.Address(True, True, xlA1, True)
      
    N.RefersTo = "=" & R1.Address(True, True, xlA1, True)
    Debug.Print N.RefersTo
End Sub

 

[xzaqus]

Many thanks.

This was really useful. I ended up modifying it a bit.

As I needed to select the second range through an InputBox, I created a temp Range which I appended to the existing using union and then updated the referTo as well as deleted the temp Range.