MattsBits
MattsBits

MattsBits

Convert Links In Excel Spreadsheet To Active Hyperlinks  

by Matt Hawkins, 02/02/2011
Categories : Microsoft .NET

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

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017