Hyperlinks are lost after sorting – VBA fix

Joseph Advanced, Excel, VBA 2 Comments

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
    1. 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.

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(1).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.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Nick K

    thanks for this tip, however this just moves the titles of the hyperlinks and not the actual hyperlink. My link say the proper name, but the filepath is to the the link that used to be in the cell prior to sorting. Any ideas?

    • Joseph

      Hi Nick, are you saying that:
      1. You select the cells that have the hyperlinks
      2. You run the VBA code from this post
      3. You sort the selected range of hyperlinks
      4. The cell’s text has been sorted, but the hyperlinks did not follow them? In other words, if you have a hyperlink in A1 and after sorting the text for A1 moved to A10, but the hyperlink remained in A1?

      And how did you create the hyperlink? Through a formulas like =Hyperlink() or by right-clicking the cell and adding a hyperlink? (or by just typing the filepath like \myservermyfile and pressing enter?)