Deleting Duplicated Names in Excel

Names scoped at Worksheet level are often duplicates

When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.

Macros can’t be undone so always save your file before running this macro. It can also be worthwhile using a different version number in case problems appear later. After running the macro check it hasn’t had any unintended consequences.

You can run this macro any time, but usually once you are finished doing all the sheet copying.

(If you are unsure about running macros see this post)

Below is an example of how the range names get duplicated.

Cell A1 was named test1 in Sheet1. When I copy Sheet1, cell A1 in the new sheet is also named test1, but at the worksheet level. You can see that in the Name Manager – image below.

The first name listed is scoped with the new Worksheet name, not Workbook as per the second name listed. The first name listed is in the copied sheet.

By the way, you can’t edit the scope of a name once it has been created. You have to delete the name and then re-create it with the correct scope.

The macro below goes through the Names collection and identifies Names with a Workbook scope (parent is the Workbook). When it finds one it goes through the Names collection again and finds any names that match the name that are scoped at the Worksheet level (parent is a Worksheet) and it deletes them. The sheet based names have the ! as part of their name.

If an error is encountered a message box is displayed.

Sub Remove_Duplicate_SheetBased_Names()
'removes all names with a Worksheet scope when there is
' the same name with a Workbook scope
Dim n As Name, nn As Name
Dim strName As String
On Error GoTo ErrorHandler
For Each n In Names
    If TypeOf n.Parent Is Workbook Then
        For Each nn In Names
            If TypeOf nn.Parent Is Worksheet Then
                strName = Right(nn.Name, Len(nn.Name) - InStr(nn.Name, "!"))
                If n.Name = strName Then nn.Delete
            End If
        Next nn
    End If
Next n
Exit Sub
MsgBox "Error encountered - macro stopped", , "ERROR"
End Sub


Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.