I have a named range (A1:A6) which is called MyRange. Lets say each cell is
populated with a number A1 = 1, A2 = 2. A3 = 3 etc. In the row (B1:B6)
beneath it, I have entered =MyRange in each cell. As such the values in
B1:B6 mirror those in A1:A6. That is, even though I use MyRange in each cell
in Row B, Excel knows which cell to pull from in rowA to correctly populate
the cells in Row B.

Now If I use the Macro below to delete the named ranges I get the following
range in each of the RowB cells =A1:A6. Once again Excel knows which cell to
pull from in Row A to populate cells in Row B.

My question is how can I adjust the macro below so that it deletes the named
range but replaces it with eact cell address that is being pulled into Row B.
So in B2 I do not want to see =A1:A6 I want to see =A2.

What I am looking to do here is replace a spreadsheets named cells/ranges
with exact cell address.

In fact I think Microsoft should proivde switch in Excel to allow the user
to do this!!!!

Sub DenameFormulas()
'This code replaces named cells with actuall address behind them
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Cells.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

Thanks