使用VBA在Excel中删除残破的命名范围 [英] Delete Broken Named Ranges in Excel Using VBA

问题描述

更大的宏的一部分涉及检测和删除在将表移动/复制到主工作簿时无意中重复的命名范围.这些中断的"命名范围由名称管理器中的值"列指示，显示为"#REF！".

Part of a much larger macro involves the detection and deletion of Named Ranges that were unintentionally duplicated upon the Move/Copy of a sheet to the main workbook. These "broken" Named Ranges are indicated by the "Value" column in the Name Manager showing "#REF!".

我尝试使用以下宏删除这些命名范围:

I've attempted the deletion of these named ranges using the following macro:

Sub DeleteBrokenNamedRanges()
Dim NR As Name
Dim numberDeleted As Variant

numberDeleted = 0
For Each NR In ActiveWorkbook.Names
    If InStr(NR.Value, "#REF!") > 0 Then
        NR.Delete
        numberDeleted = numberDeleted + 1
    End If
Next

MsgBox ("A total of " & numberDeleted & " broken Named Ranges deleted!")

End Sub

不幸的是，返回值为0，并且没有删除命名范围.我玩过保护/取消保护和InStr的一些参数，但没有任何效果.

Unfortunately, the return value is 0 and no Named Ranges were deleted. I played around with protect/unprotect and some parameters of InStr, but nothing has worked.

侧面说明-NR.Value的返回不是#REF！或预期的类似错误代码，但实际上是=C:\Blahblah\blarg.xls路径.

Side Note - The return of NR.Value is not #REF! or similar error code as expected, but is actually the =C:\Blahblah\blarg.xls path instead.

在此方面的任何帮助将不胜感激，谢谢！

Any help on this would be greatly appreciated, thanks!

推荐答案

我不认为错误代码作为范围值存在(不是我对此有任何权威).如果有效范围内有#REF以外的错误！这可能是一个问题，但我认为类似

I don't think error codes exist as range values (not that I'm any authority on this). If valid ranges had errors other than #REF! it could be a problem, but I think something like

IsError(NR.RefersToRange)

也许是检查损坏的命名范围的好方法吗?

Might be a good way to check for broken named ranges?

这篇关于使用VBA在Excel中删除残破的命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！