Thanks so much for jumping in here, Dave. On open, I cycle through
the worksheets, capture some values and write them into another sheet,
then set a range to that data with a name matching the worksheet
name. On close, I cycle through all the worksheet names and all the
range names - if there's a match, that named range gets cleared and
the name deleted. Or that's the intent, anyway.

Here's the full code, revised with what you just gave me - but it
still doesn't work.

For Each wks In Me.Worksheets
For Each nm In wks2.Names
str = nm.Name
str = Right(str, (Len(str) - InStr(1, str, "!")))
If str = wks.Name Then
Stop
nm.RefersToRange.ClearContents
nm.Delete
End If
Next nm
Next wks

Ed


On Dec 16, 7:05*am, Dave Peterson wrote:
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 -