VBA to convert a text Column to clickable Hyperlinks

[JohanGduToit]

Greetings Experts,

I have a spreadsheet in which column "I", Row 2 to 'Bottom (Variable) contains text values (i.e. https://api.blahblahblah.com/imagefile.png)

What would the vba code be to apply the "Hyperlink" formulae to the entire Column "I", or alternatively to convert values in Column "I" to
clickable hyperlinks?

Thanking you in advance.

 

[JohanGduToit]

Greetings Experts,

I have a spreadsheet in which column "I", Row 2 to 'Bottom (Variable) contains text values (i.e. https://api.blahblahblah.com/imagefile.png)

What would the vba code be to apply the "Hyperlink" formulae to the entire Column "I", or alternatively to convert values in Column "I" to
clickable hyperlinks?

Thanking you in advance.

Found the solution on another thread, hope I did not waste anybody's time!

Solution also 'rename' the underlying hyperlink to another value contained in another column.

Here goes...

Dim rCell as Range

For Each rCell In .Application.Range("I2:I" & .Application.Cells(Rows.Count, "I").End(xlUp).Row)
            .Application.ActiveSheet.Hyperlinks.Add Anchor:=rCell, Address:=rCell.Value, TextToDisplay:=rCell.Offset(, -3).Value
Next rCell

 

[davesexcel]

This will loop through column A and turn the web addresses to hyperlinks.

Sub Hyper_It()
    Dim sh As Worksheet, I As Range, rng As Range
    Set sh = ActiveSheet
    With sh
        Set rng = .Range("I2:I" & .Cells(.Rows.Count, "I").End(xlUp).Row)
        For Each I In rng.Cells
            .Hyperlinks.Add Anchor:=.Cells(I.Row, I.Column), Address:=I.Value, TextToDisplay:=I.Value
        Next
    End With

End Sub

 

[JohanGduToit]

Morning Dave,

Thank you for your suggestion; appreciated.

What would the code be to only convert the text values if there are an actual value in cells within column "I" ? In other words, to ignore any <blank> (null) cells in Column "I" and only do the conversion for cells containing an actual value?