VBA: Delete All Named Ranges

January 16th, 2005 | Categories: VBA | Tags:

To delete all Named Ranges in a Workbook, you can use the following code:

Sub DeleteNamedRanges()
Dim MyName As Name

For Each MyName In Names
    ActiveWorkbook.Names(MyName.Name).Delete
Next

End Sub

For determining whether a name exists, adding a named range, and a few other examples, Chip Pearson has a nice tutorial on working with Named Ranges.

Leave a comment | Trackback
  1. Rob van Gelder
    January 16th, 2005 at 19:37
    Reply | Quote | #1

    Hi Mark,

    When you write For Each MyName In Names
    it is the same as writing For Each MyName in Application.Names

    Application.Names and ActiveWorkbook.Names are equivilent.

    So you could simply write: Names(MyName.Name).Delete
    or better still: MyName.Delete

    Sub DeleteNamedRanges()
    Dim MyName As Name

    For Each MyName In ActiveWorkbook.Names
    MyName.Delete
    Next
    End Sub

    Cheers,
    Rob

  2. Mark
    January 16th, 2005 at 20:07
    Reply | Quote | #2

    Thanks Rob. Less code, same solution, much better :-)