This code is probably better though, post it onto a vba module run it
on your workbook:
Sub ClearNamedRanges()
'The following subroutine will parse through all formulas on all sheets
within the
'active workbook. It will then parse through all named ranges within
the active workbook
'as well, and if the named cell exists in the current formula, it will
replace the name
'with the address that the name refers to (w/out '$'). If the referred
to cell is on the
'same sheet as the formula, the sheet name will be stripped from the
cell reference
On Error Resume Next
Dim response
response = MsgBox("Please note that depending on the number of
formulas, this process may take a very long time." + vbCrLf + "Please
do not assume that your computer is locked up; a confirmation will be
displayed when the process is complete." + vbCrLf + "Would you like to
continue?", vbExclamation + vbYesNo)
If response = vbYes Then
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim Nm As Name, tempNm As Name
Dim Ref As String
Dim count As Integer
count = 0 'used to keep track of the total number of changes
For Each Sh In ActiveWorkbook.Worksheets
'Set Sh = ActiveWorkbook.ActiveSheet
Set Rng = Sh.Cells.SpecialCells(xlCellTypeFormulas)
If Rng.count >= 1 Then
For Each c In Rng
' The following chunk of code will ensure that if
we have multiple matches, it will only replace the longest (that is,
the most complete) match
' For example, if there is the named range MyRange1
and MyRange10, we wouldn't want MyRange10 to be replaced w/ the
MyRange1 reference leaving a 0 at the end...
Set tempNm = Nothing
For Each Nm In ActiveWorkbook.Names
If InStr(1, c.Formula, Nm.Name) > 0 Then 'if a
match exists
If tempNm Is Nothing Then 'if we haven't
found a previous match
Set tempNm = Nm
Else ' if we have found a previous match
If Len(tempNm.Name) < Len(Nm.Name) Then
'determine which match has the most characters and use that one
Set tempNm = Nm
End If
End If
End If
Next Nm
'Now do the actual replace:
If Not tempNm Is Nothing Then
Ref = Replace(Replace(Replace(tempNm.RefersTo,
"$", ""), "=", ""), Sh.Name & "!", "") 'setup the replace string before
replacing
c.Formula = Replace(c.Formula, tempNm.Name,
Ref) 'If it can't find the name within the formula, no replace will
happen
count = count + 1
End If
Next c
End If
Next Sh
End If
MsgBox "Process complete. A total of " & count & " named ranges
were changed", vbOKOnly + vbInformation
End Sub
