Why excel delete all the pasted hyperlinks, if I later removed any hyperlink from any one of them?

user_17797849 0
Viewed 310

I am using the below code to copy hyperlink from one cell to a range.
The hyperlinks have been pasted and working as it should,
But if I later removed any hyperlink from any one of pasted hyperlinks ,then all hyperlinks from destination cells are deleted !!
This issue happens either I have used vba (copy, paste & delete) or used merely excel.
I am using office 2016.

Sub Hyperlinks_Issue()
 
    Range("R2").Copy Range("N2:N15")
 
    Range("N2").Hyperlinks.Delete
 
End Sub
hyperlink vba excel
1 Answers

The problem has to do with the fact that Range("R2").Copy Range("N2:N15") attaches the hyperlink in R2 to the entire range of N2:N15. You can check this by inserting the following code immediately afterwards:

Debug.Print (Range("N2:N15").Hyperlinks.Count)

This returns 1, meaning the entire range only has one hyperlink (instead of 14, as you might be expecting). If then you check this:

Debug.Print (Range("N2").Hyperlinks(1).Range.Address)

You will see that it returns $N$2:$N$15. Hence, when you use Range("N2").Hyperlinks.Delete, you are simply deleting the one hyperlink that is attached to the entire range.

To avoid this, loop through all the cells in your destination range and attach the hyperlink for each cell individually. E.g.

Sub copyHyperlinks()

For Each myCell In Range("N2:N15")

    Range("R2").Copy myCell

Next myCell

Debug.Print (Range("N2:N15").Hyperlinks.Count) '= 14

End Sub

Now, Range("N2").Hyperlinks.Delete will only delete the hyperlink in this particular cell.

user_184706920
Related