Macro to remove all names from workbook

K

Katherine

I've inherited a workbook that is littered with literally hundreds of named ranges unecessarily and its causing problems when moving a sheet from one workbook to another. Because i can't select more than one name at once in the insert | name | define box, its taking me an eon to delete them individually.

Any suggestions for a macro to do this?

TIA ;)
 
P

Pete McCosh

Katherine,

this quick macro should get rid of them for you:

Sub DeleteAllNames()

Dim Nm As Name

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

End Sub

'Cheers, Pete.
-----Original Message-----
I've inherited a workbook that is littered with literally
Click to expand...
hundreds of named ranges unecessarily and its causing
problems when moving a sheet from one workbook to another.
Because i can't select more than one name at once in the
insert | name | define box, its taking me an eon to delete
them individually.
 
N

Nicky

Hi
try this

Sub delete_all_names()
errs = ""
For n = ActiveWorkbook.Names.Count To 1 Step -1
On Error Resume Next
ActiveWorkbook.Names(n).Delete
If Error <> "" Then errs = errs & Chr(13)
ActiveWorkbook.Names(n).Name
Next
If errs <> "" Then MsgBox "These names could not be deleted:" & errs

End Su
 
C

chris

This will do the job

Public Sub RemoveRngNms(
Dim cnt As Singl
On Error Resume Nex
cnt = ActiveWorkbook.Names.Coun
If cnt = 0 Then Exit Su
Do While ActiveWorkbook.Names.Count >
ActiveWorkbook.Names(1).Delet
Loo
End Su


----- Katherine wrote: ----

I've inherited a workbook that is littered with literally hundreds of named ranges unecessarily and its causing problems when moving a sheet from one workbook to another. Because i can't select more than one name at once in the insert | name | define box, its taking me an eon to delete them individually.

Any suggestions for a macro to do this?

TIA ;)
 
Top