deleting named ranges on a worksheet (not workbook)

  • Thread starter Thread starter BRC
  • Start date Start date
B

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
 
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
 
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
 
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.
 
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 -
Click to expand...

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.
 
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.
Click to expand...

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