Resizing a named range deletes the range's Name structure

Question

Whenever I try to either resize or otherwise change the cell location for a range, e.g. "A1:B1" to "A1:B2" the Name structure associated with the range is deleted. a watch entry for range.Name will be shown in the value column as

Application-defined or object-defined error

This does not seem to matter whether there is a name associated with it or not. In fact, changing the Name by means of RefersTo = automatically deletes the name structure. The following code was derived from the answer to Question 11031891, How do I redefine a named range with VBA?

    Option Explicit

Enum eColumn
  FIRSTCOLUMN = 1
  SECONDCOLUMN = 2
End Enum

Enum eRow
  FIRSTROW = 1
  SECONDROW = 2
End Enum

  Public Const scFred As String = "Fred"
  Public Const scFreda As String = "Freda"

  Public Const scAbsoluteValueRange As String = "AbsoluteValueRange"
  Public Const scResizeRange As String = "ResizeRange"

Sub ResizeIt()

  Dim iRow As Integer
  Dim nAbsoluteValue As Name
  Dim nResize As Name
  Dim rAbsoluteValue As Range
  Dim rResize As Range
  Dim ws As Worksheet

  Set ws = ActiveSheet

  '      ****                   Create Ranges                    ***

  ws.Names.Add Name:=scAbsoluteValueRange, RefersTo:=Cells(FIRSTROW, FIRSTCOLUMN)
  ws.Names.Add Name:=scResizeRange, RefersTo:=Cells(SECONDROW, SECONDCOLUMN)
  Set rAbsoluteValue = Range(scAbsoluteValueRange)
  Set rResize = Range(scResizeRange)
  Set nAbsoluteValue = ws.Names.Item(scAbsoluteValueRange)
  Set nResize = ws.Names.Item(scResizeRange)

  '      ****                   Change Ranges                    ***

  ' give an absolute reference:
  With nAbsoluteValue
    .RefersTo = ws.Cells(SECONDROW, FIRSTCOLUMN)
  End With

  iRow = FIRSTROW
  rAbsoluteValue(iRow) = scFred

  ' or, resize relative to old reference:
  With nResize
    .RefersTo = .RefersToRange.Offset(-1).Resize(2).Select
  End With

  iRow = FIRSTROW
  rResize(iRow) = scFreda

End Sub

If I run the code as far as the end of the 'Create Ranges phase I obtain two ranges:

AbsoluteValue occupying "A1" Resize occupying "B2"

The watch window confirms this

Watch : + : nAbsoluteValue : "=Sheet1!$A$1" : Name/Name : Module1.ResizeIt

Watch : + : rAbsoluteValue.Name : "=Sheet1!$A$1" : Variant/Object/Name : Module1.ResizeIt

Watch : + : nResize : "=Sheet1!$B$2" : Name/Name : Module1.ResizeIt

Watch : + : rResize.Name : "=Sheet1!$B$2" : Variant/Object/Name : Module1.ResizeIt

I then change the location, by use of the Name object, of AbsoluteValue to "A2"

In the Watch window I get

Watch : + : nAbsoluteValue : "=Sheet1!$A$2" : Name/Name : Module1.ResizeIt

Watch : : rAbsoluteValue.Name : : Variant/Integer : Module1.ResizeIt

However when I try to write to cell, "A2" (what should be the first row in the range), the result, Fred, goes in "A1". In other words the range location hasn't shifted ed For the Resize range I get a similar result:

Watch : + : nResize : "=Sheet1!$B$1:$B$2" : Name/Name : Module1.ResizeIt

Watch : : rResize.Name : : Variant/Integer : Module1.ResizeIt

However when I try to write to cell, "B1" (what should be the first row in the range), the result, Freda, goes in "B2". Again, the range location hasn't shifted

The Name Manager indicates that the cells have altered to the correct location in both instances

Tim Williams Tim Williams · Accepted Answer · 2019-03-12T18:32:42

Your question is difficult to follow but I don't see any problems:

Sub Tester()

    Dim theName As Name

    Set theName = ActiveSheet.Names.Add(Name:="myName", RefersTo:=ActiveSheet.Range("A1"))

    showIt theName  '>> Name:Sheet1!myName          Address:$A$1

    theName.RefersTo = ActiveSheet.Range("B1:B3")

    showIt theName  '>> Name:Sheet1!myName          Address:$B$1:$B$3

    theName.RefersTo = theName.RefersToRange.Resize(6, 4)

    showIt theName  '>> Name:Sheet1!myName          Address:$B$1:$E$6

End Sub

Sub showIt(nm As Name)
    Debug.Print "Name:" & nm.Name, "Address:" & nm.RefersToRange.Address
End Sub

BTW this looks like a typo:

.RefersTo = .RefersToRange.Offset(-1).Resize(2).Select

Select doesn't belong there

EDIT You do this:

 Set rResize = Range(scResizeRange)

now rResize points to the range defined in scResizeRange, but there's no link between rResize and your named range - the two are independent. So if you change the named range and then later do this:

 rResize(iRow) = scFreda

...then it would be expected that you'd write to the original location of the named range

Resizing a named range deletes the range's Name structure

1 Answers