Hardcore users only - help needed to clean up a Workbook

ecovonrein 

Remove hidden data and personal information by inspecting documents, presentations, or workbooks

"Ghost links" in excel

Removing Unknown Links

Maybe these links will help you a little further with your topic.

fyi

I opened your file in Excel 2016.

You can try to create a new, simple workbook with external links and defined names referencing other workbooks. Run the same macros on this simpler file to see if Excel loses track of links here as well. If not, the issue may lie with the complexity or corruption of the original workbook.

Your point about using FOR EACH or FOR i is valid. While iterating backwards (using STEP -1) helps prevent issues when modifying a collection, the problem here seems unrelated to the iteration method. Rather, as far as I have interpreted the translation correctly, to be due to a bug related to how Excel handles external links when working with defined names.

Here is a refined version of your macro to attempt the same idea (preserving links):

Option Explicit
'The code is untested, backup your file first.
Sub preserveExternalLinks()
    Dim n As Long, i As Long
    Dim nm As Name
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim extLinks As Collection
    Set extLinks = New Collection

    ' Create a hidden worksheet to store temporary link references
    On Error Resume Next ' If sheet already exists
    Set ws = ThisWorkbook.Sheets("TempLinkSheet")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "TempLinkSheet"
        ws.Visible = xlSheetVeryHidden ' Hide it from the user
    End If
    On Error GoTo 0

    lastRow = 1
    n = ThisWorkbook.Names.Count
    
    ' Iterate backwards to avoid issues with index shifting
    For i = n To 1 Step -1
        Set nm = ThisWorkbook.Names(i)
        
        ' If the name refers to an external workbook (links contain brackets [ ])
        If InStr(nm.RefersTo, "[") > 0 Then
            ' Store the external reference in a hidden worksheet
            extLinks.Add nm.RefersTo
            ws.Cells(lastRow, 1).Formula = nm.RefersTo
            lastRow = lastRow + 1
        End If
        nm.Visible = True
    Next i
    
    MsgBox "External links preserved and handled."

End Sub

Hope I helped a little...if not just ignore my post.🙂