XL98: Macro to Delete Defined Names with Links

MORE INFORMATION

To Create a Macro to Delete Defined Names with Links

1. Press OPTION+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type the following code:

       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
   						

4. On the Tools menu, click Macros. Select "delete_external_names" and click Run.