Deleting Range Names

[Maudi]

Hi! I've been working on Excel for a few years. In
Excel '97 when you delete a range name, it reverts back to
its original cell address/range. It is said that the same
must happen in Excel 2000. I've tried various different
options but once a range name is deleted it produces an
error where it was used in a formula or reference. Any
ideas?
Thank you, in advance!

 

[Juan Sanchez]

Maudi...

I tryed for a non-vba method of doing this but couldnot
figure it out... so I wrote a macro that replaces all the
names in the active worksheet with the "definition" of
those names. Once you have replaced them you can delete
them and have no errors on your formula...

Paste this into a module on your personal macro book or a
module on any workbook and then run it from the tools
menu... try it first on a non-sesitive workbook since I
can not guarantee it will work. It replaces ALL referenced
names in the active sheet with the cells they represent.

Sub ReplaceNames()

Dim MyName As String
Dim MyRef As String
Dim MySheet As String

MySheet = ActiveSheet.Name

For Each Name In Application.Names

If MySheet = Mid(Name.RefersTo, 2, _
Application.WorksheetFunction.Find("!", Name.RefersTo) -
2) Then
MyRef = Right(Name.RefersTo, Len(Name.RefersTo) _
- Application.WorksheetFunction.Find("!", Name.RefersTo))
Else
MyRef = Right(Name.RefersTo, Len(Name.RefersTo) - 1)
End If
MyName = Name.Name

Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Replace What:=MyName, Replacement:=MyRef,
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

Next

End Sub

Cheers.... Juan Sanchez

 

[Maudi]

Dear Juan

Thank you very much! I've just started training on Excel
VBA so this is going to be a great macro to test.

Have a great day!

-Maudi