[Excel VBA] How to Bulk Delete All “Defined Names” in a Workbook [Caution]

Introduction

Excel’s “Defined Names” feature is very useful for giving specific cell ranges easy-to-understand names for use in formulas. However, as you repeatedly copy sheets or have multiple people edit a file, unnecessary definitions and broken links can accumulate.

These unnecessary definitions can cause the workbook to become unstable or the file size to bloat. While deleting them one by one from the “Name Manager” dialog is tedious, you can use a VBA macro to clean up all “Defined Names” in the workbook at once.

In this article, I will explain the simple VBA code to achieve this, along with important precautions for execution.

⚠️ IMPORTANT: Warning Before Execution

This macro will delete ALL “Defined Names” registered in the workbook.

If you delete names that are currently used in functions like VLOOKUP or INDIRECT, your formulas will break and display #NAME? errors. Before running this, please ensure you make a backup of your file or only use it on files where you are certain that deleting names will not cause issues.

VBA Sample Code to Delete All Defined Names

This macro loops through every name (.Names) existing in the workbook (ActiveWorkbook) and deletes them one by one (.Delete).

' Delete all defined names in the current workbook
Sub DeleteAllDefinedNames()

    '== Define variables ==
    Dim definedName As Name
    Dim nameCount As Long
    Dim userResponse As VbMsgBoxResult

    ' Count names before deletion
    nameCount = ActiveWorkbook.Names.Count
    
    If nameCount = 0 Then
        MsgBox "There are no Defined Names in this workbook.", vbInformation
        Exit Sub
    End If
    
    '== Ask user for final confirmation ==
    userResponse = MsgBox(nameCount & " Defined Names exist in this workbook." & vbCrLf & _
                          "Do you want to delete ALL of them?" & vbCrLf & _
                          "(This action cannot be undone)", _
                          vbExclamation + vbOKCancel, "Final Confirmation")

    '== Execute only if OK is pressed ==
    If userResponse = vbOK Then
    
        '== Delete all names in the workbook via loop ==
        For Each definedName In ActiveWorkbook.Names
            definedName.Delete
        Next definedName
        
        MsgBox "All Defined Names have been deleted.", vbInformation
        
    Else
        MsgBox "Operation cancelled.", vbInformation
    End If

End Sub

Explanation of the Code

Dim definedName As Name

This declares the variable definedName to store a Name object. The Name object refers to the “Defined Name” itself in Excel.

userResponse = MsgBox(…)

As mentioned earlier, the action of this macro is destructive and cannot be undone. Therefore, a message box appears before execution to ask the user for final confirmation. This is an important step to improve safety.

For Each definedName In ActiveWorkbook.Names

This is the core of the process. Using the For Each ... Next syntax, the code retrieves names one by one from the ActiveWorkbook.Names collection (all “Defined Names” in the currently active workbook) and runs the loop.

definedName.Delete

Inside the For Each loop, the .Delete method is executed on each retrieved definedName object to remove the definition.

Summary

In this article, I introduced a VBA macro to bulk delete unnecessary “Defined Names” accumulated in a workbook.

• You can retrieve all names in a workbook using ActiveWorkbook.Names.
• You can achieve bulk deletion by combining a For Each loop with the .Delete method.
• Backup and confirmation before execution are essential because important formulas may result in errors.

This macro is very useful for maintaining files that have unintentionally become bloated or unstable. Please understand the risks fully and use it carefully.