reset defined ranges

[Greg]

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

 

[Chip Pearson]

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

 

[Gary''s Student]

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)

 

[Greg]

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