Excel Macro: Deleting All Defined Names

This Excel VBA macro deletes all the defined names from an active workbook.

Macro Example

Sub DeleteDefinedNames()

  Dim nm As Name

  For Each nm In ActiveWorkbook.Names
    nm.Delete
  Next nm

End Sub

Description

Variable Declaration

Sub...

  Dim nm As Name

The 3rd line declares an object variable named nm refers to a Name object that represents the defined name for a range of cells.

obj-Excel-Name

Starts Looping

Sub...

  Dim nm As Name

  For Each nm In ActiveWorkbook.Names

The 5th line starts to loop through all the elements (=Name) of a collection (=Names).

(Application.)ActiveWorkbook

  For Each nm In ActiveWorkbook...

The Application.ActiveWorkbook property returns an active Workbook object.

obj-Excel-Application-Activeworkbook

The ActiveWorkbook property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-Activeworkbook

ActiveWorkbook.Names (Workbook.Names)

  For Each nm In ActiveWorkbook.Names

The Workbook.Names property returns a Names collection object that represents all the names in a workbook.

obj-Excel-Workbook-Names

Deleting a Name

Sub...

  Dim nm As Name

  For Each nm In ActiveWorkbook.Names
    nm.Delete

The 6th line deletes a defined name by the Name.Delete method.

obj-Excel-Name-Delete

Loops Back

The 7th line loops back to get the next name.

Sub...

  Dim nm As Name

  For Each nm In ActiveWorkbook.Names
    nm.Delete
  Next nm

After every defined name is deleted, this macro ends.

Sub DeleteDefinedNames()

  Dim nm As Name

  For Each nm In ActiveWorkbook.Names
    nm.Delete
  Next nm

End Sub

Properties

http://www.relief.jp/itnote/archives/001970.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007