MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting range names using a macro


Posted by Andre on May 15, 2000 2:33 AM

I would like to remove a range name from the names list
after deleting the values in this range. Uptil now I
was able to delete the contents but never the name itself. Can somebody help me?


Posted by Celia on May 15, 2000 5:44 AM


Andre
Go to Insert/Name/Define.
Select the name you want to delete so that it is in the "Names in Workbook" box.
Click "Delete"

If you want to do it in a macro, use the macro recorder to record the steps per above which should produce the following code :-

ActiveWorkbook.Names("NameToDelete").Delete

Celia

Posted by Ruth Ann Francis on May 15, 2000 8:13 AM

As an alternative, the following macro will
delete all the names in a workbook.

Sub delete_all_names()
' Delete all Existing Range Names
oldstatusbar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient ... Deleting Existing Range Names"
num = ActiveWorkbook.Names.count
For i = 1 To num
Application.StatusBar = "Please be patient ... Deleting Existing Range Names" & i
ActiveWorkbook.Names(1).Delete
Next i
Application.StatusBar = oldstatusbar

Beep
MsgBox "And We're Done!", vbOKOnly
End Sub