Macro to Delete Defined Names with Links

MORE INFORMATION

Visual Basic Code Example

 Option Explicit

 Sub delete_external_names()

 'variable declarations
 Dim response As Integer
 Dim msg As String
 Dim flag As Boolean
 Dim defined_name As Object

   flag = True     ' check if external links were found

   ' loop through each defined name in workbook
   For Each defined_name In ActiveWorkbook.Names

      ' if a [ was found, then the name has a link
      If InStr(defined_name.RefersTo, "[") > 0 Then

         flag = False ' set flag to False indicating a link was found

         ' Message displayed to ask if you want to delete name
          msg = "Do you want to delete the defined name " & "'" & _
             defined_name.Name & "'" & Chr(13) & " that refers to '" & _
             defined_name & "' ?"

          ' delete the defined name
          If MsgBox(msg, 292) = vbYes Then defined_name.Delete
       End If
   Next defined_name  ' get the next defined name

   If flag = True Then  ' if flag was not set, display message below

      MsgBox "No defined names with external were links found."
   End If
 End Sub