deleting named ranges on a worksheet (not workbook)

[BRC]

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

 

[Bernard Liengme]

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

 

[Don Guillett]

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

 

[Rick Rothstein]

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.

 

[BRC]

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)






- 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.

 

[Rick Rothstein]

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.