EXCEL VBA Macro help

[ajeanneau]

Hi:

I've been trying to write a macro to be able to open multiple hyperlinks all at once. Currently, I have to check thousands of URLs, some of which get removed due to copyright infringement on YouTube. My objective is to be able to track those URLs that have been deleted.

I found the following code:

Sub Open_Hyperlinks()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, "A").Hyperlinks.Count > 0 Then
Cells(i, "A").Hyperlinks(1).Follow
End If
Next
End Sub

The problem is that when hyperlinks are removed, the code no longer works. Is it possible to write a code so that it can highlight or change the format on error values?

Thanks!

 

[tlowry]

Here's my suggestion...

Sub Open_Hyperlinks()
    Dim i, LastRow, status
    On Local Error GoTo Errors
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
        status = "Testing"
        Select Case Cells(i, "A").Hyperlinks.Count
           Case 0
                status = "No Link"
           Case Else
                Cells(i, "A").Hyperlinks(1).Follow
                If status = "Testing" Then status = "Good"
        End Select
        Cells(i, 2) = status
    Next i
    Exit Sub
Errors:
    status = Err.Description
    Err.Clear
    Resume Next
End Sub

 

[ajeanneau]

Thanks for your suggestion, and it works perfectly!!

Thank you so much!!