Excel VBA Cells and Hyperlinks
I track some information in Excel, and one of the columns is for a link to some location (blogs, Web sites, etc.). One of the things is that I don't want the URL to appear as it creates display issues (as shown here).
I really want it to look like this:
I prefer to just display the word "Link" that is marked up with the actual link. Problem is-it's time consuming to go through and do this to many dozens of links manually as I work along. It's way faster for me to write the following lines of VBA and quickly solve the problem. Now, I keep adding rows and just re-run the macro periodically to dress it all up.
Private Sub ConvertToLink()
Dim oCell As Range
Dim iRow As Integer
Dim iCount As Integer
iRow = Cells.SpecialCells(xlLastCell).Row
For iCount = 2 To iRow
oCell = Cells(iCount, 5)
If oCell.Text <> "Link" And Len(oCell.Text) > 0 Then
oCell.Hyperlinks.Add(oCell, oCell.Text, , , "Link")
End If
Next iCount
End Sub
Rock Thought of the Day:
I posted the best 10 rock albums of 2009 with commentary here: https://bit.ly/5hRSOw. The top 3 are these:
Modest Mouse: The Moon & Antarctica
U2: No Line On The Horizon
Iggy Pop: Préliminaires
Rock On
Technorati Tags: VBA,Office 2010,Excel,Office,Macro,Microsoft,Developer