Thanks to one and all!! It's working!!

Ed


On Dec 16, 8:06*am, "Rick Rothstein"
wrote:
You can protect against clearing/deleting names that don't refer to ranges
by doing something like this...

Dim Dummy As Variant
Dim N As Name
On Error Resume Next
For Each N In wks2.Names
* Dummy = N.RefersToRange
* If Err.Number = 0 Then
* * N.RefersToRange.ClearContents
* * N.Delete
* End If
* Err.Clear
Next

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message

...



Maybe...

dim wks2 as worksheet
dim nm as name
set wks2 = me.worksheets("what's the name of wks2???")

for each nm in wks2.name
*on error resume next
*nm.referstorange.clearcontents
*on error goto 0
*nm.delete
next nm

This will delete names that don't refer to ranges, too!

Ed from AZ wrote:

Using Excel 2007. *In a Workbook_Open sub, I set some named ranges
using
* * * wks2.Names.Add _
* * * * Name:=nm, _
* * * * RefersToR1C1:=rg

I am trying to clear the contents of these ranges and delete the names
on Workbook_BeforeClose using
* For Each nm In Me.Names
* * * wks2.Range(nm).ClearContents
* * * wks2.Range(nm).Delete
* * * Me.Names(nm).Delete
* Next nm

It's not working! * The data is still there, and the Names Manager
shows the names still there. I've got to finish this one today. *Can I
get a bit o' help, please?

Ed

--

Dave Peterson- Hide quoted text -

- Show quoted text -