Automatic Check for Dead Links in Excel

I use this Excel macro that I found at http://www.excelbanter.com/showthread.php?t=120564

. . . all the time to confirm that hyperlinks are active. It won't work if you've got your hyperlinks set up with the =HYPERLINKS formula. A work around (there's probably a better way) is to paste the column from the spreadsheet into Word and then re-paste back into Excel. (Be sure to remove any line breaks (find and replace ^l with a blank space; the macro won't work if there are merged cells). This converts the formulas to active hyperlinks. Also remove stray hyperlinks, maybe web addresses that take up only part of an entry in a cell. The macro will stall out if these are left in. Deliberately break one of the links so you can be sure the macro worked. It will generate a new worksheet with a list of the bad links you need to correct.

Sub ChkHypLnks()

Dim wksHypLnks, wksBadHypLnks As Worksheet

Dim curHypLnk As Hyperlink

Dim curFile As String

Dim iBadHypLnks As Integer

Set wksHypLnks = ActiveSheet

Set wksBadHypLnks = ThisWorkbook.Worksheets.Add

wksBadHypLnks.Name = "BadHypLnks" _

& Right(wksBadHypLnks.Name, Len(wksBadHypLnks.Name) - 5)

For Each curHypLnk In wksHypLnks.Hyperlinks

If Dir(curHypLnk.Address) = "" Then

iBadHypLnks = iBadHypLnks + 1

wksBadHypLnks.Cells(iBadHypLnks, 1) = curHypLnk.Address

End If

Next curHypLnk

Application.DisplayAlerts = False

If iBadHypLnks < 1 Then wksBadHypLnks.Delete

Application.DisplayAlerts = True

End Sub