Counting Hyperlinks

Question

I am trying to debug a one-line macro:

Sub Hypercounter()

MsgBox ActiveSheet.UsedRange.Hyperlinks.Count

End Sub

If I start with an empty worksheet:

1. enter a hyperlink in a cell
2. copy the cell
3. paste separately into several disjoint cells

and run the macro, it correctly reports the total number of hyperlinks on the sheet.

If, however, I paste into a block of cells, the macro reports 2 even though hundreds of cells now contain hyperlinks.

Why?

Answer 1

If you paste (a cell with) a hyperlink to a range, that entire range becomes the anchor of the hyperlink, and the target range will have a single hyperlink.

For example, consider the following code:

Sub TestHyperlink()

    ' Remove all existing hyperlinks

    ActiveSheet.Hyperlinks.Delete

    ' Add hyperlink to cell A1

    ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="http://www.eileenslounge.com"

    ' Copy A1 to C2:Z100

    Range("A1").Copy Destination:=Range("C2:Z100")

    ' The sheet now contains two hyperlinks:

    MsgBox "Sheet contains " & ActiveSheet.Hyperlinks.Count & " hyperlinks"

    ' The anchor of the second hyperlink is C2:Z100:

    MsgBox "The anchor of the second hyperlink is " & ActiveSheet.Hyperlinks(2).Range.Address

End Sub

Answer 2

Thanks!