column of strings, column of hyperlinks - how to combine?

**jeff23** · 08-17-2010, 08:46 PM

Hey guys, I'm making my first macro and having trouble.

In one column I have the names of reports, and in the adjacent column I have the URL hyperlinks for the reports. I'd like to have the names of the reports hyperlinked to the URLs directly so that I can just delete the messy column of web addresses.

Any ideas for a simple script that can do this? Recording the macro doesn't work because Excel copies the links' cells, not their addresses. I have about 500 to do total in a few different columns and sheets, but the format is the same.

The format is like this:
A | B
KFC | http://www.kfc.com/
McDonalds | http://www.mcdonalds.com/
Burger King | http://www.burgerking.com/

Thanks!
Jeff

**JBeaucaire** · 08-17-2010, 10:38 PM

Like so:

Option Explicit

Sub InsertHyperlinks()
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row
Dim RNG As Range:   Set RNG = Range("C1:C" & LR)
Dim cell As Range

For Each cell In RNG
    cell.Formula = "=HYPERLINK(""" & cell.Offset(, -1) & """,""" & cell.Offset(, -2) & """)"
Next cell

Range("A:B").Delete xlShiftToLeft
Set RNG = Nothing
End Sub