Deleting Excel Named Ranges

< Back to Search results

 Category: [Excel]  Demo Available 

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


        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.



stars (0 Reviews)