Wednesday, March 6, 2013

Change Cell Url Contents to Hyperlinks using VBA in MS Excel

Assuming in your MS Excel Worksheet you have thousands of cells that contains url and you want them to be a hyperlink. It can be done manually, but tedious.

Another solution is to write a VBA (Visual Basic for Applications) Script to do that for you using the Sub procedure below:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Sub ChangeCellsToHyperlinks()  
   Dim rng As Range  
   Dim ctr As Integer  
   Dim range_name As String  
   ctr = 2 'starting cell to change  
   Do While ctr <= 10000 'end cell range  
     Set rng = ActiveSheet.Range("A" & ctr)  
     rng.Parent.Hyperlinks.Add Anchor:=rng, Address:=rng  
     With rng.Font  
       .ColorIndex = 25  
       .Underline = xlUnderlineStyleSingle  
     End With  
     ctr = ctr + 1  
   Loop  
 End Sub

That's it!

0 comments:

Post a Comment