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