清理有错误的命名范围地址(例如 #REF！

Question

我设置了一个命名范围，我们叫他RngIn。他有3个牢房，地址是A1：A3

接下来，我删除第 2 行。

我的 RngIn 现在在其 RefersTo 属性中（正确）显示 #REF！ 错误："=A1，工作表1！#REF！，A2"

这意味着我无法使用 VBA 操作该命名范围的其余部分，因为全局对象的方法"范围"错误。

范围是在过程中创建的，如果用户随后出于某种原因需要删除一行，我未来的代码将失败，因为它需要知道其余命名范围数据在哪里......

我已经尝试了许多方法来访问VBA中此范围的其余地址信息，但到目前为止失败了，例如

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!", "")

理想情况下，我希望在文本字符串中看到 RngIn 的结果是："=A1，A2"因为 A2 现在是最初在 A3 中的数据的位置。

Answer 1

不确定我是否很好地理解了这一点：您的示例代码不使用定义名称（又名命名范围）。假设您创建一个名为 RangeIn 的名称，该名称引用 A1、A3、A5，然后删除第 3 行。

RangeIn 的 RefersTo 现已=Sheet1!$A$1,Sheet1!#REF!,Sheet1!$A$4

此代码删除 Sheet1！#REF！，以保留名称范围在引用=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

Answer 2

在这种情况下，我将命名范围的起点和终点设置为用户可以删除的范围上方的单元格和下方的单元格，然后使用 OFFSET 或 INDEX 函数调整该范围的大小以排除我的书签。或者我使用 Excel 表格，它可以处理行删除而不会返回 #REF 错误。