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.
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:
- You have a range of hyperlinks which are NOT formulas that use the
HYPERLINK()function (they are created by Insert >> Hyperlink)
- Copy the range and paste it elsewhere in the workbook
- So far, the pasted hyperlinks should be fine
- Sort the recently pasted hyperlinks
- 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.
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
- Copy-paste the range of hyperlinks
- Select the newly pasted range
- Run the code
- 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
- Loops through each cell in the selected range
- Checks if the cell has a hyperlink
- Temporarily grab and store the address that it’s pointing to
- 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?
Still not convinced? Check out my review of the course!
Suggest the next post!
I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!