Deleting Defined Names

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Does anyone know of a macro that will delete Defined Names from a workbook, rather than going in and deleting each Name from the Insert-Name-Define function?

Cheers

pvr928
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Have you tried recording a macro while deleting just one Named Range and then modifying it?
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
nbrcrunch

Yes, but it doesn't help with the structure of a macro to delete a large number of Names.

I have found this macro on the Board:

Sub RemoveNames()

Dim MyName, Response

For Each MyName In ThisWorkbook.Names

Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)

If Response = vbYes Then MyName.Name.Delete

Next MyName

End Sub

but it is coming up with a 424 run time error, requiring an Object for the 'MyName.Name.Delete' function.

Any help greatly appreciated.

cheers

pvr928
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi pvr928:

Try the following ...
Code:
Sub RemoveNames()
    Dim MyName, Response
        For Each MyName In ThisWorkbook.Names
            Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)
            'Yogi Anand: If Response = vbYes Then MyName.Name.Delete ... doesn't work for me in EXCEL 97 ... use the following line instead
            If Response = vbYes Then MyName.Delete
        Next MyName
End Sub
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Thanks Yogi & Aladin.

Yogi - I got either a run time or property error depending on which of the MyName.Delete or MyName.Name.Delete I used - any suggestions?

Aladin - the Name Manager looks awesome!! - but my system won't let me download the add-in.

Cheers

pvr928
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yogi's code:

Code:
Sub RemoveNames()
    Dim MyName, Response
        For Each MyName In ThisWorkbook.Names
            Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)
            
            If Response = vbYes Then MyName.Delete
        Next MyName
End Sub

works perfectly for me - if you copy the above code and insert it into your module and run it does it still give you the error?

What line of code does it stop on?

Best regards

Richard
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Parsnip

It stops on the 'MyName.Delete' function.

I think I have figured out why it does that - it is because the Name it is trying to delete has a 'REF#!" error in it.

I do have a macro that deletes all Names that have a 'REF#!" error in them - but this remaining Name is a ghost link that I can't seem to track down - it appears to be connected with a chart, but I cannot for the life of me find it in the Workbook.

If the Name does not have an error in it, the macro does work as intended.

I'd appreciate any assistance in tracking down this ghost link.

Cheers

pvr928
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,652
Members
412,335
Latest member
cinciri99
Top