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
