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 ;)

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
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 ;)
.

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

--
Message posted from http://www.ExcelForum.com

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 ;)

You can also download the Name Manager, a free add-in that makes it easy
to work with defined names:

http://www.bmsltd.ie/MVP/Default.htm

under the heading for Jan Karel Pieterse


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 ;)


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html