Iterate code to add hyperlink with excel vba

Question

I have code that creates hyperlink in one cell. I want it to iterate through all filled cells along column F or C or E.

Sub insertVeryLongHyperlink()

Dim curCell As Range
Dim longHyperlink, TextToDisplay1 As String

Set curCell = Range("G1")  ' or use any cell-reference
longHyperlink = [E1]
TextToDisplay1 = [C1]

curCell.Hyperlinks.Add Anchor:=curCell, _
                Address:=longHyperlink, _
                SubAddress:="", _
                ScreenTip:=" - Click here to follow the hyperlink", _
                TextToDisplay:=TextToDisplay1

End Sub

Do you mean the hyperlink is in column E and the textToDisplay is in column C and you want to iterate over the columns and add a hyperlink to cells in column G? — Alex P
– Alex P, Commented Mar 14, 2017 at 12:59
Yes, you are right. I want to create hyperlinks till last row in table. Please find snapshot in main post. — Samadhan Gaikwad
– Samadhan Gaikwad, Commented Mar 14, 2017 at 13:10
Enclose your code in For...Loop and iterate over all rows. — ManishChristian
– ManishChristian, Commented Mar 14, 2017 at 13:13

Alex P · Accepted Answer · 2017-03-14 13:52:06Z

2

This produces hyperlinks in column G.

Sub InsertVeryLongHyperlink()
    Dim cl As Range

    For Each cl In Range("G1:G" & Range("G1").End(xlDown).Row)
        cl.Hyperlinks.Add Anchor:=cl, Address:=cl.Offset(0, -2).Text, TextToDisplay:=cl.Offset(0, -4).Text, ScreenTip:=" - Click here to follow the hyperlink"
    Next cl
End Sub

cl.Offset(0, -2).Text is column E
cl.Offset(0, -4).Text is column C

edited Mar 14, 2017 at 13:52

answered Mar 14, 2017 at 13:42

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samadhan Gaikwad Over a year ago

Great but need to change till last row. Right now range is fixed, can you help me to make range variable according to total row count?

Samadhan Gaikwad Over a year ago

Thanks, it's working great, just made one correction ("F1") instead of ("G1").

Collectives™ on Stack Overflow

Iterate code to add hyperlink with excel vba

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related