Hyperlinks are lost after sorting - VBA fix

After I wrote my post about creating hyperlinks with external data, a subscriber told me about a problem they were having and was wondering if I could help.  The issue was that when they sorted hyperlinks after they were recently copied from one range to another, some hyperlinks would be removed from the range while others remained intact.  I hadn’t run into this issue before, but after some research and a little VBA coding, I created a quick workaround.  I’d like to share that workaround with you.

The Setup

I’ve tried to replicate this issue, but I haven’t been able to actually create a workbook from scratch where I can demonstrate this issue.  Even on Microsoft’s support page mentions that this error may occur.

However, I’ll at least give you the basic steps to understand what’s happening:

1. You have a range of hyperlinks which are NOT formulas that use the HYPERLINK() function (they are created by Insert >> Hyperlink)

2. Copy the range and paste it elsewhere in the workbook

   • So far, the pasted hyperlinks should be fine
3. Sort the recently pasted hyperlinks
4. Some of the hyperlinks in the sorted list may not work anymore

The subscriber that had the issue sent the workbook to me and it was interesting because I was able to replicate it only sometimes.  It wasn’t consistent. Either way, the user didn’t want to have to check all of the links every time he pasted it and sorted it.  So I came up with a VBA solution that ensured the links would remain intact.

Does this article help you? If so, please consider supporting me with a coffee ☕️

The Solution

Since the hyperlinks would be removed once the range was sorted, the solution that worked for me was to rebuild the hyperlinks before he sorted it.

Here is the VBA code:

Option Explicit

Sub fixHyperlinks()
    Dim rng As Range
    Dim address As String

    Application.ScreenUpdating = False

    For Each rng In Selection
        If rng.Hyperlinks.Count > 0 Then
            address = rng.Hyperlinks(rng.Hyperlinks.Count).address

            rng.Hyperlinks.Add Anchor:=rng, _
                                address:=address
        End If
    Next

    Application.ScreenUpdating = True
End Sub

To use this code

1. Copy-paste the range of hyperlinks
2. Select the newly pasted range
3. Run the code
4. Sort the range

If you’re not very familiar with VBA, you can check out Anthony’s VBA Tutorials.  I always liked his tutorials when I was first starting out.

How it works

1. Loops through each cell in the selected range
2. Checks if the cell has a hyperlink
3. Temporarily grab and store the address that it’s pointing to
4. Re-apply the hyperlink

After you run this code, you should be able to sort the range without losing your links.