Would anyone have some code that I could run to delete all named ranges
on all woksheets within a file?


Thanks

To remove the names, but leave the data intact:

For Each nName In ActiveWorkbook.Names
nName.Delete
Next

To clear the ranges of data but leave the names:

For Each nName In ActiveWorkbook.Names
On Error Resume Next
nName.RefersToRange.ClearContents
On Error Goto 0
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sean" wrote in message
oups.com...
Would anyone have some code that I could run to delete all named ranges
on all woksheets within a file?


Thanks

Thanks Jon, its just to remove the names and leave everthing else
intact. I'll get it a go


Jon Peltier wrote:

To remove the names, but leave the data intact:

For Each nName In ActiveWorkbook.Names
nName.Delete
Next

To clear the ranges of data but leave the names:

For Each nName In ActiveWorkbook.Names
On Error Resume Next
nName.RefersToRange.ClearContents
On Error Goto 0
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sean" wrote in message
oups.com...
Would anyone have some code that I could run to delete all named ranges
on all woksheets within a file?


Thanks

if for some reason you wanted to make a backup of the ranges, or maybe duplicate
ranges from one workbook to another, you could use this. then just take the
output of the immediate window, paste it in a sub and run it to re-create the
ranges. sheet names would obviously have to be identical.

Sub BackupRanges()
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & nm.Name & """" & _
", Refersto:=""" & nm
Next
End Sub
--


Gary


"Sean" wrote in message
oups.com...
Thanks Jon, its just to remove the names and leave everthing else
intact. I'll get it a go


Jon Peltier wrote:

To remove the names, but leave the data intact:

For Each nName In ActiveWorkbook.Names
nName.Delete
Next

To clear the ranges of data but leave the names:

For Each nName In ActiveWorkbook.Names
On Error Resume Next
nName.RefersToRange.ClearContents
On Error Goto 0
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sean" wrote in message
oups.com...
Would anyone have some code that I could run to delete all named ranges
on all woksheets within a file?


Thanks