Remove dead links

[schwede]

Hi!

I've asked a similar question before, but didn't get very far, so please bear with me.

My problem is the following: I often find myself with excel sheets with a ton (sometimes several hundred) of defined names, most of which are dead. With dead, I mean that they are either literally dead ("#REF") or are linked to other files.

I've written the code below but sometimes get an 1004 - "That name is not valid" error in line "n.delete".

Anyone have any suggestions on how to solve this?

Thanks a lot!

Cheers,
Schwede

Sub RemoveDeadNames()
Dim n As Name
Dim Total As Integer
Dim WrongRef As Integer
Dim External As Integer
Dim Remaining

Total = 0
WrongRef = 0
External = 0
Remaining = 0

With ActiveWorkbook
For Each n In ActiveWorkbook.Names
  Total = Total + 1
  If InStr(1, n, "#REF") > 0 Then
    n.Delete
    WrongRef = WrongRef + 1
  ElseIf InStr(1, n, ".xls") > 0 Then
    If InStr(1, n, ActiveWorkbook.Name) = 0 Then
      n.Delete
      External = External + 1
    End If
  End If
Next n
End With

For Each n In ActiveWorkbook.Names
  Remaining = Remaining + 1
Next n

MsgBox "Total number of names: " & Total & Chr(13) & Chr(10) _
  & "Wrong references deleted: " & WrongRef & Chr(13) & Chr(10) _
  & "External links deleted: " & External & Chr(13) & Chr(10) _
  & "Remaining names: " & Remaining
End Sub

 

[BrianB]

Range Names in Excel are objects in their own right and can exist on several levels - so need special handling.

'==============================================================================
'- SHOW RANGE NAMES IN WORKBOOK & WORKSHEETS
'- NB. DOES NOT SHOW NAMES IN CHARTS
'- An Excel Range Name is a 'Name Object' with its own set of properties.
'- Can be set at WorkBook or WorkSheet level (so allowing duplicate names)
'- Brian Baulsom July 2007
'==============================================================================
Sub SHOW_NAMES()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim Nm As Name
    Dim NmRefers As String
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    '-------------------------------------------------------------------------
    Set wb = ActiveWorkbook
    '- MAKE NEW WORKSHEET
    Set ToSheet = Worksheets.Add
    ToSheet.Cells.ClearContents
    ToSheet.Range("A1:D1").Value = Array("Location", "Name", "RefersTo", "Value")
    ToRow = 2
    '-------------------------------------------------------------------------
    '- WORKBOOK NAMES
    If wb.Names.Count > 0 Then
        For Each Nm In wb.Names
            Nm.Visible = True
            ToSheet.Cells(ToRow, 1).Value = wb.Name
            ToSheet.Cells(ToRow, 2).Value = Nm.Name
            NmRefers = "'" & Nm.RefersTo                'FORCE TO TEXT
            ToSheet.Cells(ToRow, 3).Value = NmRefers
            ToSheet.Cells(ToRow, 4).Value = Nm.Value    ' if single cell
            ToRow = ToRow + 1
        Next
    End If
    '-------------------------------------------------------------------------
    '- WORKSHEET NAMES
    For Each ws In wb.Worksheets
        If ws.Names.Count > 0 Then
            For Each Nm In ws.Names
                Nm.Visible = True
                ToSheet.Cells(ToRow, 1).Value = ws.Name
                ToSheet.Cells(ToRow, 2).Value = Nm.Name
                NmRefers = "'" & Nm.RefersTo               'FORCE TO TEXT
                ToSheet.Cells(ToRow, 3).Value = NmRefers
                ToSheet.Cells(ToRow, 3).Value = Nm.Value   ' if single cell
                ToRow = ToRow + 1
            Next
        End If
    Next
    '------------------------------------------------------------------------
    ToSheet.UsedRange.Columns.AutoFit
    MsgBox ("Done")
End Sub
'-------------------------------------------

 

[schwede]

Brian,

thanks for your reply and your code which I have ammended a bit to do the actual deleting (see below). I still get a 1004 "That name is not valid" error on wb.Names(Nm.Index).Delete

The ws.Names(Nm.Index).Delete command seems to work fine. Could there be an issue with double names?

'==============================================================================
'- SHOW RANGE NAMES IN WORKBOOK & WORKSHEETS
'- NB. DOES NOT SHOW NAMES IN CHARTS
'- An Excel Range Name is a 'Name Object' with its own set of properties.
'- Can be set at WorkBook or WorkSheet level (so allowing duplicate names)
'- Adaped from Brian Baulsom July 2007
'==============================================================================
Sub SHOW_NAMES()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim Nm As Name
    Dim wbwsSwitch As Boolean
    Dim NmRefers As String
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    '-------------------------------------------------------------------------
    Set wb = ActiveWorkbook
    '- MAKE NEW WORKSHEET
    Set ToSheet = Worksheets.Add
    ToSheet.Cells.ClearContents
    ToSheet.Range("A1:D1").Value = Array("Location", "Name", "RefersTo", "Value")
    ToRow = 2
    On Error GoTo errorhandler
    '-------------------------------------------------------------------------
    '- WORKBOOK NAMES
    If wb.Names.Count > 0 Then
        wbwsSwitch = True
        For Each Nm In wb.Names
            Nm.Visible = True
            If InStr(1, Nm.RefersTo, "#REF") > 0 Then
                wb.Names(Nm.Index).Delete
            Else
                ToSheet.Cells(ToRow, 1).Value = wb.Name
                ToSheet.Cells(ToRow, 2).Value = Nm.Name
                NmRefers = "'" & Nm.RefersTo                    'FORCE TO TEXT
                ToSheet.Cells(ToRow, 3).Value = NmRefers
                ToSheet.Cells(ToRow, 4).Value = Nm.RefersTo     ' if single cell
                ToRow = ToRow + 1
            End If
        Next
    End If
    '-------------------------------------------------------------------------
    '- WORKSHEET NAMES
    For Each ws In wb.Worksheets
        wbwsSwitch = False
        If ws.Names.Count > 0 Then
            For Each Nm In ws.Names
                Nm.Visible = True
                If InStr(1, Nm.RefersTo, "#REF") > 0 Then
                    Nm.Delete
                Else
                    ToSheet.Cells(ToRow, 1).Value = ws.Name
                    ToSheet.Cells(ToRow, 2).Value = Nm.Name
                    NmRefers = "'" & Nm.RefersTo               'FORCE TO TEXT
                    ToSheet.Cells(ToRow, 3).Value = NmRefers
                    ToSheet.Cells(ToRow, 4).Value = Nm.Value   ' if single cell
                    ToRow = ToRow + 1
                End If
            Next
        End If
    Next
    '------------------------------------------------------------------------
    ToSheet.UsedRange.Columns.AutoFit
    MsgBox ("Done")
    
errorhandler:
    If wbwsSwitch = True Then
        wb.Names(Nm.Index).Delete
    Else
        ws.Names(Nm.Index).Delete
    End If
    Resume Next
End Sub
'-------------------------------------------

Thanks for your help!

Cheers,
Schwede

 

[BrianB]

One of the benefits of setting an object variable is the ability to use its methods. So to delete the name with my code (bearing in mind you need it in 2 places) you just need to add :

Nm.Delete