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

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

To clear the contents of named cell and then remove the names:

Sub clear_um()
For Each n In ActiveWorkbook.Names
Range(n.Name).Clear
n.Delete
Next
End Sub

--
Gary''s Student - gsnu200820


"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

Ps. I don't like to put this kind of code in the workbook_beforeclose event.

It means that I have to save for these changes to take effect--and there may be
other changes that I don't want saved.

I'll either use a dedicated macro (run it on demand) or even the workbook_open
event so that things are nice the next time someone opens the file (and macros
are allowed to run).

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

--

Dave Peterson

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 -

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

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 -