The following macro allows you to convert a whole column of plain text urls in an Excel spreadsheet into active clickable hyperlinks.

The variables intStartRow and intStartCol determine what cell the macro starts from. It continues looking for text in the specified column until it encounters an empty cell.

Sub Links2Hyperlinks()

'

' This functions converts links contained in a column

' to hyperlinks

' http://www.mattsbit.co.uk/

'

 

' Specify what cell to start in

intStartRow = 2

intStartCol = 5

 

intRowCounter = intStartRow

 

' Convert links until empty cell is found in column

Do Until Cells(intRowCounter, intStartCol).Value = ""

 

  ' Select cell

  Range(Cells(intRowCounter, intStartCol), _

        Cells(intRowCounter, intStartCol)).Select

        

  ' Create hyperlink

  ActiveSheet.Hyperlinks.Add _

        Anchor:=Selection, _

        Address:=Cells(intRowCounter, intStartCol).Value, _

        TextToDisplay:=Cells(intRowCounter, intStartCol).Value

        

  ' Increment row counter 

  intRowCounter = intRowCounter + 1

 

Loop

 

End Sub