Delete error Defined Names

Location on ribbon: Tab DVD Addin → Group Cells and Rows

Command ID: btnDeleteErrorName

What it does

The command scans every Defined Name in the active workbook and deletes the broken ones: names whose reference contains # (typically #REF!) or \ (pointing at an external file that no longer exists), plus every name that is hidden. These names are the usual reason Excel keeps asking "A name already exists" each time you copy a sheet, and why a quantity workbook received from another party balloons in size and opens slowly.

Run it right after receiving a file from a subcontractor or consultant, or after splitting sheets out of a large workbook.

How to use

1. Open the workbook you want to clean and make it the active workbook (the command works on ActiveWorkbook).
2. Click DVD Addin → Cells and Rows → Delete error Defined Names. No selection is required.
3. While scanning, the Excel status bar shows progress such as Scanning name 300/1250.
4. When finished, the status bar reports the result: Deleted 812/1250 broken/hidden named ranges. Hold ESC to stop early.
5. Save the workbook (Ctrl+S) to keep the result.

Example

You receive Subcontractor BoQ - Block A.xlsx. Every time you copy the "Structure quantities" sheet into your master file, Excel raises a duplicate-name prompt hundreds of times. Open the file, click Delete error Defined Names — the status bar reports 812 broken or hidden names removed. Save, then copy the sheet again: no more prompts, and the file size drops noticeably.

Notes

• Ctrl+Z cannot undo this — keep a copy of the file before running it on important records.
• The command also deletes hidden names (Visible = False) even when their reference is still valid. If the workbook relies on hidden names for array formulas or print areas, verify the formulas afterwards.
• Locked or protected names are skipped silently and do not abort the run.
• With no workbook open, the status bar shows "No workbook is open" and nothing happens.
• Hold ESC to stop early; names already deleted before the stop stay deleted.

Related

• Strip junk styles — clears the junk Cell Styles that also bloat and slow the file.
• Delete unused formats — shrinks the used range after names are cleaned.
• Delete blank rows — the other clean-up step usually done on incoming files.