Find and Replace Hyperlinks

Sub FindReplaceHLinks(sFind As String, sReplace As String, _
    Optional lStart As Long = 1, Optional lCount As Long = -1)
   
    Dim rCell As Range
    Dim hl As Hyperlink
   
    For Each rCell In ActiveSheet.UsedRange.Cells
        If rCell.Hyperlinks.Count > 0 Then
            For Each hl In rCell.Hyperlinks
                hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
            Next hl
        End If
    Next rCell
End Sub

Sub Doit()
   
    FindReplaceHLinks “F:help”, “F:SystemHelp”
   
End Sub

Posted in Uncategorized

11 thoughts on “Find and Replace Hyperlinks

  1. this is a little simpler (from the helpfile example)

    For Each h in ActiveSheet.Hyperlinks

  2. Dick,

    Here is how I would write it:

    Sub FindReplaceHLinks(sFind As String, sReplace As String, _
        Optional lStart As Long = 1, Optional lCount As Long = -1)
       
        Dim hl As Hyperlink
       
        For Each hl In ActiveSheet.Hyperlinks
            If Len(hl.SubAddress) Then
                hl.SubAddress = Replace(hl.SubAddress, sFind, sReplace, lStart, lCount, vbTextCompare)
            Else
                hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
            End If
        Next
           
    End Sub

    There are a couple of advantages this way.

    Firstly, it iterates through just the Hyperlinks collection of the ActiveSheet, as opposed to each of the cells in the UsedRange, which is likely a much smaller collection.

    Secondly, it works for both internal and external links, instead of just external.

    Regards,

    Daniel Ferry
    excelhero.com

  3. As a follow-up, it should be noted that neither of our methods will work for hyper links that are in a sheet as a result of the HYPERLINK worksheet function.

  4. Wouldn’t a simple global find and replace be more appropriate, since if you want hyperlinks to point to a new location, you want anything to point there as well? Or am I missing something.

  5. Dick Kusleika and Daniel Ferry,
    Thank you very much for the fix to the ‘Roaming’ problem. Last time I did a manual replace. It took days. This time I found Mr. Ferry’s version of Mr. Kusleika’s solution. It worked perfectly. It took me all of four minutes to implement.
    Eric

Leave a Reply

Your email address will not be published. Required fields are marked *