Defined Names Issue

[CarlCarlos]

All,

Please help !

I have finally found out why some of my spreadsheets become huge sizes. It has something to do with "Phantom Links", I have no idea how these link to my spreadsheet and I am unable to break them, this results in 1000's of Defined Names (Insert / Names / Defined Names).

How can i delete ALL of these quickly as doing them one by one will take FOREVER !!!

I have looked at previous posts and none of those Marcros would work, for some reason it would always fail at the 'Delete' line

 

[VoG]

Try

Sub DeleteAllNames()
Dim i As Long
For i = ActiveWorkbook.Names.Count To 1 Step -1
      Range(ActiveWorkbook.Names(i).Name).Name.Delete
Next
End Sub

 

[CarlCarlos]

Thank you for such a quick reply, Just put it through VBA (insert modual) and has the following error:

run time error 1004
Method of 'Range' of object '_Global' failed

any ideas?

 

[VoG]

That did not error for me.

Excel User Tip: Locate phantom links in a workbook may help you.

 

[GTS]

Defined names and links are a couple different issues...

To handle named ranges in Excel, I use the free Name Manager by JKP (Jan Karel Pieterse). Just google that and you'll find it immediately. It puts Excel's name handling on steroids.

I've also used the FindLink addin by Bill Manville for trying to deal with link issues.

A couple utilities you can try. Hope it helps.

GTS

 

[CarlCarlos]

Darn,

Just had a look at the link and it is point two causing all the issues... I have 1000's of pages of names in there (probably caused by this work file being rolled over time and time again)
I can only assume that it has failed because some of the Defined Names may be causing an issue with the Macro?

Because i am at work, i am unable to download any addins

 

[GTS]

Darn,

Because i am at work, i am unable to download any addins

I hear you. Sigh. Our IT dept is quite strict as well. They don't allow any 'outside' programs. So far, they haven't bothered me about a few Excel Addins that I have. Fingers crossed that it stays that way.

btw, I've also used ASAP Utilities in the past (briefly). IIRC, it also has a mass name deleting feature.

I have had to fix stuff like this at home when one doesn't have the tools they need at work.

Good luck.

 

[VoG]

Then try this

Sub DeleteBadNames()
Dim nm As Excel.Name
Dim vTest As Variant
For Each nm In ActiveWorkbook.Names
    vTest = Empty
    On Error Resume Next
    vTest = Application.Evaluate(nm.RefersTo)
    On Error GoTo 0
    If TypeName(vTest) = "Error" Then nm.Delete
Next nm
End Sub