Deleting Multiple Names

[TomCat]

I have acquired a spreadsheet that has been used for many different
applications by many people. Consequently, several hundred names have been
applied to cells and/or references to other spreadsheets. Each time I creat a
copy of one of the worksheets, a dialogue box appears asking me if I want to
keep or rename the name that has been applied to cells in the sheet. I have
to either hit OK or rename all of them.

What I want to do is delete ALL of the names and start with a clean sheet
each time I copy it. It's very cumbersome deleting them one at a time in the
INSERT - NAME - DEFINE box.

How can I perform a MASS Name Delete?

Thanks.........TC

 

[Per Jessen]

Hi

You can remove all names in the active workbook with this workbook.

Sub RemoveNames()
For Each n In ActiveWorkbook.Names
n.Delete
Next
End Sub

To inser the code:
Alt+F11 to open the VBA editor
Goto Insert > Module
Copy the above code to the code sheet
Run the macro.

Hopes this helps.

 

[TomCat]

Thanks for the reply, Per. I tried it and got an error on the n.Delete.

Sub RemoveNames()
For Each n In ActiveWorkbook.Names
n.Delete <------------error
Next
End Sub

 

[Bob Phillips]

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

 

[TomCat]

Only problem is that there are a couple of hundred names in this sheet and
all are different names. I would have to list all of the names to do the
macro below. Is there some way to just delete all the names without listing
each one? Mass delete of n...? The sheet is formatted very intricately and
I'm trying to keep from recreating it several times in new workbooks (which
would solve the problem...).

 

[Bob Phillips]

No you don't, that is an exclusion list to avoid deleting system generated
names.

 

[Tim Roberts]

Bob, I've a similar situation where I was pulling a lookup table from one spreadsheet to another. It was riddled with NAME references. I got tired of looking at it and found your post.

This worked like a charm!

Thanks very much for your post.



Bob Phillips wrote:

Re: Deleting Multiple Names
19-Feb-09

Sub DeleteNames(
Dim nme As Nam

For Each nme In ActiveWorkbook.Name
If nme.Name Like "*_FilterDatabase" Or
nme.Name Like "*Print_Area" Or
nme.Name Like "*Print_Titles" Or
nme.Name Like "*wvu.*" Or
nme.Name Like "*wrn.*" Or
nme.Name Like "*!Criteria" The
Els
nme.Delet
End I
Next nm

End Su

--
_________________________________
HT

Bo


EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx