reset defined ranges
 

hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

--
______
Regards,
Greg

reset defined ranges
 

No, you cannot restore a name back to some previous reference. Once
you change the location to which a name refers, you can't
automatically go back to some prior value.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Tue, 11 May 2010 11:30:02 -0700, Greg
wrote:

hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

reset defined ranges
 

This little macro will replace each Named Range in each formula with the
orgiinal range:

Sub FixNames()
Dim n As Name, nn As String, naddy As String
Dim r As Range
For Each n In ActiveWorkbook.Names
nn = n.Name
naddy = Right(n.RefersTo, Len(n.RefersTo) - 1)
For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next
Next
End Sub

So if alpha is:
A1:A3
and beta is:
B1:B3

formulas like:
=SUM(alpha)
will become:
=SUM(Sheet1!$A$1:$A$3)
--
Gary''s Student - gsnu201002


"Greg" wrote:

hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

--
______
Regards,
Greg

reset defined ranges
 

does not work. I think there is some error with the below loop. Excel is
trying to open some file to update values. The ranges do not have external
references though

For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next




"Gary''s Student" wrote:

This little macro will replace each Named Range in each formula with the
orgiinal range:

Sub FixNames()
Dim n As Name, nn As String, naddy As String
Dim r As Range
For Each n In ActiveWorkbook.Names
nn = n.Name
naddy = Right(n.RefersTo, Len(n.RefersTo) - 1)
For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next
Next
End Sub

So if alpha is:
A1:A3
and beta is:
B1:B3

formulas like:
=SUM(alpha)
will become:
=SUM(Sheet1!$A$1:$A$3)
--
Gary''s Student - gsnu201002


"Greg" wrote:

hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

--
______
Regards,
Greg