VBA清除范围内的单元格名称

Question

在执行过程之前，我需要清除范围内所有命名单元格的名称。

到目前为止，我已经尝试了以下代码但没有成功:

Sub EraseCellNames()

Worksheets("SheetA").Range("A1:G5").Select
Dim nName As Name
    For Each nName In Names
        If Not Intersect(Selection, Range(nName.Name)) Is Nothing Then
            nName.Delete
        End If
    Next nName

End Sub

有任何想法吗？

提前致谢!

Answer 1

试试这个:

Sub EraseCellNames()
    Dim nName As Name
    Dim targetRng As Range

    Set targetRng = Worksheets("SheetA").Range("A1:G5")

    For Each nName In ThisWorkbook.Names
        If nName.ValidWorkbookParameter Then
            If targetRng.Parent.Name = nName.RefersToRange.Parent.Name Then
                If Not Intersect(targetRng, nName.RefersToRange) Is Nothing Then nName.Delete
            End If
        End If
    Next nName
End Sub