deleting named ranges on a worksheet (not workbook)
 

Hi All
I am writhing a routine that takes a data set that is pasted in and
then does several calculations. in the process it defines several
ranges. I would like to have a line of code (or subroutine)that would
delete any named ranges in the worksheet to make certian we are
starting with clean sheet. Any help is appreciated. Thanks BRC

deleting named ranges on a worksheet (not workbook)
 

This deletes all names in a workbook

Sub tryme()
Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
'MsgBox nms.Count
mylast = nms.Count
For r = mylast To 1 Step -1
' MsgBox nms(r).Name
nms(r).Delete
Next r
End Sub

I have commented out some debugging statements that I used to get it to
work. I had forgotten you must work from the end of a collection when
deleting!
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"BRC" wrote in message
...
Hi All
I am writhing a routine that takes a data set that is pasted in and
then does several calculations. in the process it defines several
ranges. I would like to have a line of code (or subroutine)that would
delete any named ranges in the worksheet to make certian we are
starting with clean sheet. Any help is appreciated. Thanks BRC

deleting named ranges on a worksheet (not workbook)
 

Sub delnamesonactivesheet()
sl = Len(ActiveSheet.Name)
For Each n In ActiveWorkbook.Names
If Mid(n, 2, sl) = ActiveSheet.Name Then n.Delete
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BRC" wrote in message
...
Hi All
I am writhing a routine that takes a data set that is pasted in and
then does several calculations. in the process it defines several
ranges. I would like to have a line of code (or subroutine)that would
delete any named ranges in the worksheet to make certian we are
starting with clean sheet. Any help is appreciated. Thanks BRC

deleting named ranges on a worksheet (not workbook)
 

Give this a try...

Sub DeleteNamesFromActiveSheet()
Dim N As Name
For Each N In ActiveWorkbook.Names
If N.RefersToRange.Parent.Name = ActiveSheet.Name Then N.Delete
Next
End Sub

As constructed, it removes the names from the ActiveSheet, but you can make
the obvious change if you want to remove the names from a specified
worksheet instead of the ActiveSheet.

--
Rick (MVP - Excel)


"BRC" wrote in message
...
Hi All
I am writhing a routine that takes a data set that is pasted in and
then does several calculations. in the process it defines several
ranges. I would like to have a line of code (or subroutine)that would
delete any named ranges in the worksheet to make certian we are
starting with clean sheet. Any help is appreciated. Thanks BRC

deleting named ranges on a worksheet (not workbook)
 

On Jan 9, 2:22*pm, "Rick Rothstein"
wrote:
Give this a try...

Sub DeleteNamesFromActiveSheet()
* Dim N As Name
* For Each N In ActiveWorkbook.Names
* * If N.RefersToRange.Parent.Name = ActiveSheet.Name Then N.Delete
* Next
End Sub

As constructed, it removes the names from the ActiveSheet, but you can make
the obvious change if you want to remove the names from a specified
worksheet instead of the ActiveSheet.

--
Rick (MVP - Excel)

"BRC" wrote in message

...



Hi *All
I am writhing a routine that takes a data set that is pasted in and
then does several calculations. *in the process it defines several
ranges. *I would like to have a line of code (or subroutine)that would
delete any named ranges in the worksheet to make certian we are
starting with clean sheet. *Any help is appreciated. Thanks BRC- Hide quoted text -

- Show quoted text -

Thank you all for the input. I used Don's code and it seems to work
fine. I did have to change the string read from 2 to 3. I am using
excel 2007 and it appears that range names in this version are
preceded by....='.... and the names start at the 3rd character. thanks
again.

deleting named ranges on a worksheet (not workbook)
 

Thank you all for the input. I used Don's code and it seems
to work fine. I did have to change the string read from 2 to 3.
I am using excel 2007 and it appears that range names in
this version are preceded by....='.... and the names start at
the 3rd character. thanks again.

Of course, I have no problem with you using one of the other solutions
offered to you; however, I just wanted to point out that the one I posted
requires no string manipulations at all... it simply uses the built in
properties of the the Name object.

--
Rick (MVP - Excel)