Deleting Defined Names

[pvr928]

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

 

[nbrcrunch]

Have you tried recording a macro while deleting just one Named Range and then modifying it?

 

[pvr928]

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]

Hi pvr928:

Try the following ...

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

 

[Aladin Akyurek]

Try NameManager: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm

 

[pvr928]

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]

Yogi's 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]

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