Add Hyperlinks to an Entire Column using VBA in Excel

Convert Salesforce id and name to clickable hyperlinks in an Excel table after extracting data from Power Query wrote this over 4 years ago and it was last updated over 4 years ago.

← Back to the Posts

I whipped up this procedure in Excel VBA so that I could convert the output a values in a table (usually an id and a name column) from Power Query into a clickable HTML hyperlink so the user could quickly navigate to the records homepage in Salesforce.

I used this with an existing table which made the conversion of a few thousand records in a split second. Was fun.

Option Explicit

Sub AddHyperLinks()

  Dim x As Integer
  Dim y As Integer: y = get_last_row("Report", 1)
  Dim id As String
  Dim name As String
  For x = 9 To y
    id = Cells(x, 1)
    name = Cells(x, 2)
    With ThisWorkbook.Sheets("Report")
      .Hyperlinks.Add Anchor:=.Range("B" & x), _
      Address:="" & id, _
      ScreenTip:="GoTo " & name, _
    End With
  Next x

End Sub

Function get_last_row(sheetname As String, column_number As Integer) As Long

  get_last_row = Sheets(sheetname).Cells(Rows.Count, column_number).End(xlUp).row

End Function

You may want to delete all hyperlinks from the worksheet before you begin in case you have a table that is dynamically changing. In this case add the following to the macro and call it in the first step using call RemoveHyperlinks.

Sub RemoveHyperlinks()


End Sub