Delete Broken Named Ranges: One Click to Clean Up 30 #REF! Names Left by Deleted Sheets

TL;DR: You inherit a workbook with 30 named ranges. Half of them reference sheets that were deleted two years ago. Excel’s Name Manager makes you delete them one by one, each with a confirmation dialog. This macro scans every name, finds the broken ones, lists them in a message box with visibility info (hidden or visible), asks once, and deletes them all. Seconds instead of minutes.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with named ranges — any workbook that’s been through multiple engagement years or inherited from another preparer

What the macro does:

• Scans every name in ThisWorkbook.Names — workbook-level and worksheet-level
• Identifies broken names by checking if their RefersTo string contains #REF! (fast check) or if Evaluate() returns an error (catches other error types)
• Flags hidden names (prefixed with _ or non-visible) separately in the report
• Shows the full list in a message box and asks once before deleting
• Deletes all broken names in a single pass

Limitations:

• Only checks names in ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook to run on any open file
• Does not check external workbooks — if a name references a closed workbook that was renamed (not deleted), the name may still resolve successfully even though the reference is unwanted
• The message box has a practical limit of ~1024 characters on older Excel versions. If you have more than ~40 broken names, the list may be truncated. The count will still be accurate
• This is destructive — deleted names cannot be recovered with Undo. Save before running

#The Macro

Option Explicit

Sub DeleteBrokenNamedRanges()
    ' ── Delete Broken Named Ranges ─────────────────────
    ' Scans every named range in ThisWorkbook.Names.
    ' Identifies broken names (#REF! from deleted
    ' sheets). Lists them in a message box with
    ' visibility info. Asks once before deleting all.
    '
    ' Zero configuration. Zero input. One confirmation.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim nm As Name
    Dim brokenList As String
    Dim brokenCount As Long
    Dim hiddenBroken As Long
    Dim totalNames As Long
    Dim scopeLabel As String
    Dim deleted As Long
    Dim i As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Scan for broken names ──────────────────
    brokenList = ""
    brokenCount = 0
    hiddenBroken = 0
    totalNames = ThisWorkbook.Names.Count

    For Each nm In ThisWorkbook.Names
        If IsNameBroken(nm) Then
            ' Determine scope for display
            If TypeName(nm.Parent) = "Workbook" Then
                scopeLabel = "(workbook)"
            Else
                scopeLabel = "(sheet: " & nm.Parent.Name & ")"
            End If

            ' Build list entry — keep it compact for the MsgBox
            brokenList = brokenList & "  · " & nm.Name & " " & _
                         scopeLabel

            If Not nm.Visible Then
                brokenList = brokenList & " [hidden]"
                hiddenBroken = hiddenBroken + 1
            End If

            brokenList = brokenList & vbCrLf
            brokenCount = brokenCount + 1
        End If
    Next nm

    ' ── Step 2: Report findings ────────────────────────
    If brokenCount = 0 Then
        MsgBox "No broken named ranges found among " & totalNames & _
               " total name(s).", vbInformation, "All Clear"
        GoTo CleanUp
    End If

    ' ── Step 3: Ask for confirmation ───────────────────
    Dim msg As String
    msg = "Found " & brokenCount & " broken named range(s) " & _
          "out of " & totalNames & " total:" & vbCrLf & vbCrLf & _
          brokenList

    If hiddenBroken > 0 Then
        msg = msg & vbCrLf & hiddenBroken & " of these are hidden names " & _
              "(prefixed with underscore or not visible in Name Manager)."
    End If

    msg = msg & vbCrLf & "Delete all " & brokenCount & _
          " broken name(s)? This cannot be undone."

    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Delete Broken Names?") = vbNo Then
        MsgBox "No names were deleted.", vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Step 4: Delete broken names (reverse order) ────
    deleted = 0

    For i = ThisWorkbook.Names.Count To 1 Step -1
        Set nm = ThisWorkbook.Names(i)
        If IsNameBroken(nm) Then
            nm.Delete
            deleted = deleted + 1
        End If
    Next i

    ' ── Step 5: Final report ───────────────────────────
    MsgBox deleted & " broken named range(s) deleted. " & _
           ThisWorkbook.Names.Count & " name(s) remaining.", _
           vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Check if a named range is broken ───────────
Private Function IsNameBroken(ByRef nm As Name) As Boolean
    ' Check 1: Fast string scan for #REF! in the RefersTo
    ' This catches the common case — a deleted sheet
    If InStr(1, nm.RefersTo, "#REF!", vbTextCompare) > 0 Then
        IsNameBroken = True
        Exit Function
    End If

    ' Check 2: Evaluate the name to catch other error types
    ' (#N/A, #VALUE!, etc. in named formulas)
    Dim result As Variant
    On Error Resume Next
    result = Evaluate(nm.Name)
    On Error GoTo 0

    If IsError(result) Then
        IsNameBroken = True
    Else
        IsNameBroken = False
    End If
End Function

#How It Works

#Two checks are better than one

The IsNameBroken helper function runs two tests. The first is a simple string search on the RefersTo property:

If InStr(1, nm.RefersTo, "#REF!", vbTextCompare) > 0 Then
    IsNameBroken = True
    Exit Function
End If

When a named range refers to a deleted sheet, VBA stores the broken reference as the literal text =#REF!$A$1 or =#REF!$A$1:$E$10. The #REF! string is physically present in the RefersTo property. This check is instant — no evaluation, no worksheet access, just string comparison.

But not all broken names contain the text “#REF!”. A named formula like =VLOOKUP("Missing",NonExistentRange,2,FALSE) might return #N/A without ever displaying “#REF!” in the formula text. Same for division-by-zero and other errors. That’s why the second check exists:

result = Evaluate(nm.Name)
If IsError(result) Then IsNameBroken = True

Evaluate(nm.Name) forces Excel to resolve the name and return its value. If anything about the name fails — missing sheet, circular reference, incompatible types — IsError catches it.

The two checks are ordered: the string check is first because it’s fast and catches 95% of real-world cases. The Evaluate check only runs when the string check passes, saving time on large workbooks.

#Reverse-order deletion

For i = ThisWorkbook.Names.Count To 1 Step -1
    Set nm = ThisWorkbook.Names(i)
    If IsNameBroken(nm) Then
        nm.Delete
        deleted = deleted + 1
    End If
Next i

When you delete an item from a VBA collection, the collection shrinks and the indexes shift. If you delete from 1 to Count, you’ll skip every other matching item after the first deletion. Deleting from Count down to 1 ensures every index that hasn’t been visited yet is still valid. This is a standard VBA pattern for destructive collection iteration.

#Hidden names exist, and you should know about them

Excel supports hidden names — names prefixed with an underscore or explicitly marked as not visible. They’re used for things like validation lists, print area definitions, and internal references that shouldn’t clutter Name Manager.

The macro checks nm.Visible for each broken name:

If Not nm.Visible Then
    brokenList = brokenList & " [hidden]"
    hiddenBroken = hiddenBroken + 1
End If

Hidden broken names are still counted and still deleted (they’re broken, after all). But they’re flagged separately so you know how many of the broken names you’ve never seen before. If the count is 8 broken and 5 hidden, someone else’s internal references are polluting your workbook without ever appearing in the Name Manager UI.

#Worksheet-level vs workbook-level names

If TypeName(nm.Parent) = "Workbook" Then
    scopeLabel = "(workbook)"
Else
    scopeLabel = "(sheet: " & nm.Parent.Name & ")"
End If

A name can be scoped to a specific worksheet (Sheet1!LocalName) or to the entire workbook (GlobalName). The scope determines where the name is visible — worksheet-level names only appear in Name Manager when the containing sheet is active.

The report shows the scope next to each broken name. This matters when you’re cleaning up: a broken name scoped to “Sched-E” might be intentional (a work-in-progress reference you plan to restore), while a broken workbook-level name like “Prior_Year_FA_Import” is almost certainly dead weight.

#Why the MsgBox confirms before deleting

The message box serves three purposes:

1. Review. You get to actually read the list of names before nuking them. If you see “FA_Depr_Current” in the broken list and you know that’s an active named range in a different version of the file, you click No and investigate.

2. Accountability. The report tells you how many names exist, how many are broken, and how many are hidden. This is useful context even if you decide not to delete — you now know the workbook has 12 broken names and can fix them individually.

3. The one-click decision. Name Manager forces you to confirm each individual deletion. This macro replaces ~30 clicks with one. But it doesn’t replace zero clicks with one — the single confirmation is the minimum viable safety net.

#Save before you run — there is no undo

Deleting a named range is a VBA action with no undo stack. If you delete 12 names and realize one of them was “the tax calc range the partner set up in 2022,” there’s no getting it back. Save the workbook, run the macro, review the remaining names in Name Manager, and if you regret the result, close without saving.

For extra safety, the macro only deletes names that are definitely broken. Names that resolve without errors — even if they point to ranges you don’t recognize — are left alone. The macro errs on the side of caution.

#Adapt It