CODEWITHSUNDEEP
excelvba
kevinykuo
kevinykuo

Reputation: 4772

Delete all instances of a range name

Suppose I have a workbook with multiple sheets. There are many ranges with the name Name1, at the worksheet scopes and the workbook scope. I want to write a macro that goes through the workbook to delete all instances of Name1.

For example, I open the Names Manager and I see:

Name1, refers to: =Sheet1!A1, scope: Sheet1<br>
Name1, refers to: =Sheet2!D3, scope: Sheet2<br>
Name1, refers to: =Sheet1!A1, scope: Workbook

I want to delete all of the Name1s

Now, Names("Name1").Delete, at the workbook level, doesn't work. I think it just takes care of the globally scoped Name1. Is there a way to work with a collection of names corresponding to a particular name? What's the best way to do this?

Upvotes: 0

Views: 1206

Answers (1)

kevinykuo
kevinykuo

Reputation: 4772

The following macro removes all instances of Name1 from all open workbooks

Sub deleteName()
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        Dim i As Integer
        For i = wb.Names.Count To 1 Step -1
            If wb.Names(i).Name Like "*Name1" Then wb.Names(i).Delete
        Next i
    Next wb
End Sub

Note that we need to loop backwards through the collection of names. This is because if we try to do For Each nName in wb.Names / If nName.Name Like "*Name1" Then nName.Delete / Next, Excel/VBA gives unexpected results such as Error 424 Object Required. I'm not sure why this behavior occurs.

Upvotes: 1

Related Questions