Deleting Excel Named Ranges
As most reading this page will know, Excel provides many ways to create and use Named Ranges in Excel. However, when you have a lot of named ranges it is a tedious task to delete them all, or specific named ranges. In fact, the only way via the standard Excel interface is via Insert>Name>Define one at a time!
The 2 Excel VBA Macros below can be used to delete named ranges. Use the best suited one for your circumstance.
Delete Dead (#REF!) Excel Named Ranges
Sub DeleteDeadNames() Dim nName As Name For Each nName In Names If InStr(1, nName.RefersTo, "#REF!") > 0 Then nName.Delete End If Next nName End Sub
Delete Chosen Excel Named Ranges
Sub DeleteChosenNames() Dim nName As Name Dim lReply As Long For Each nName In Names lReply = MsgBox("Delete the named range " & nName.Name _ & vbNewLine & "It refers to: " & nName.RefersTo, _ vbQuestion + vbYesNoCancel, "Ozgrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then nName.Delete Next nName End Sub
To use either of the above codes, go to Tools>Macro>Visual Basic Editor (Alt+F11) then go to Insert>Module. After pasting in the wanted code, go back to Excel proper and then to Tools>Macro>Macros and Run the chosen macro.
|Delete Module After Running VBA Code|
|Delete Excel Sheet Event Code Macro|
|Delete Excel Workbook Event Code|
Free Training Course: Lesson 1 - Excel Fundamentals