Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete all named ranges in a workbook or worksheet? | Excel Programming | |||
Deleting all named ranges that have a workbook scope | Excel Discussion (Misc queries) | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
named ranges at workbook and worksheet levels | Excel Programming |