---

Deleting Excel Named Ranges

Excel VBA: Deleting Excel Named Ranges. Delete Dead Named Ranges

 

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.

 

See also:

Delete Module After Running VBA Code

Delete Excel Sheet Event Code Macro

Delete Excel Workbook Event Code

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Gallery

---

• 
• 
• 
• 
• 
•