Search code examples
excelvbanamed-ranges

Clean up Named Range Addresses with Errors (e.g. #REF!)

I set up a named range, let's call him RngIn. He has 3 cells, and his address refers to A1:A3

Next, I delete Row 2.

My RngIn now shows #REF! error (correctly) in its RefersTo property: "=A1,Sheet1!#REF!,A2"

This means I cannot manipulate the rest of that named range using VBA, because of the Method 'Range' of Global Object error.

The range is created during a process, and if a user subsequently needs to delete one row for whatever reason, my future code will fail because it needs to know where the rest of the named range data is...

I have tried many ways to access the remaining address information for this range, in VBA, but failed so far, e.g.

Dim RngAddress As String
Dim RngIn As Range

Set RngIn = Range("A1:A3")
RngAddress = RngIn.Address
RngAddress = RngIn.RefersToRange.Address
RngAddress = RngIn.RefersTo
RngAddress = Replace(RngIn.Address, "Sheet1!#REF!", "")

What I ideally want to see in a text string as the result for RngIn is: "=A1,A2" Because A2 is now the location of the data which was originally in A3.

Solution

  • Not sure I understand this well: your example code does not use Defined Names (aka Named Ranges). lets suppose you create a Name called RangeIn that refers to A1,A3,A5 and you then delete Row 3.

    The RefersTo for RangeIn is now =Sheet1!$A$1,Sheet1!#REF!,Sheet1!$A$4

    This code removes the Sheet1!#REF!, to leave the Name RangeIn referring to =Sheet1!$A$1,Sheet1!$A$4

    Option Explicit
Option Compare Text
Sub ChangeRef()
Dim strAd As String
strAd = ThisWorkbook.Names("RangeIn").RefersTo
strAd = Replace(strAd, "Sheet1!#REF!,", "")
ThisWorkbook.Names("RangeIn").RefersTo = strAd
End Sub