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

Question

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.

Do you have more rows of data below A3? You could just redefine the named range. I use a worksheet_change event to detect the change, get the new last row and recreate the nam. Something like thisworkbook.names(“my name”).referstorange = mysheet.cells(1,1).resize(lastrow,1) — wallyeye
Hey, thanks for the suggestion, but that won't work. My range is not contiguous (the cells aren't really A1, A2, A3 but more like A70, A104, A106, etc.). I should've made that more clear in the question! — jfgoodhew1

Charles Williams Charles Williams · Accepted Answer · 2019-01-14T19:24:56

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

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

2 Answers