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.
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.
The ActiveWorkbook property is a member of the Excel.Global class, so we can omit the Application property.
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.
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.
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