Forum Discussion
"Ghost links" in excel
I had ghost links from two worksheets I copied over from an older workbook to my current one. I could not find them even after:
1. Break Links or Change Source from the Edit Links command in the ribbon
2. Checking Name Manager for ranges linked to outside workbooks
3. Removing all Conditional Formatting (this was in a spare testing copy)
4. Removing all Data Validation
5. Removing all my custom Format Styles
6. Did a Copy / Paste-Value for the two worksheets, essentially converting all formulas to hard-coded values
7. The links appeared only when I brought in the two worksheets. So there are no hidden worksheets in my current workbook that might hold these hidden links.
8. I cannot use outside utilities such as File Manager (same name as the Excel feature) that can look for hidden range names.
My final solution, which worked, is to run this code which I found in some forum which removes any hidden range names, and so I finally got the links to go away:
*********
Sub Remove_Hidden_Names()
Dim xName As Name
Dim k As Integer
Dim x As Integer
x = 0
On Error Resume Next
For Each xName In ThisWorkbook.Names
If xName.Visible = False Then
xName.Delete
x = x + 1
End If
Next xName
MsgBox x & " hidden names have been deleted.", vbInformation + vbOKOnly, "Hidden names deleted"
End Sub
Hi,
Log in to left a reply. I just want to give a a HUGE THANK because omg you've saved A LOT of my files. Thank you so so much!!