asked on

Excel VBA Hyperlink Code

I have two questions regarding hyperlink vba coding.

Question 1:

I would like to display the text "Click here" instead of the actual URL in each row. What do i need to do to achieve this?

Currently i am using the following code to generate the hyperlink

Dim i3 As Integer
Dim s3 As String

For i3 = 4 To Range("H:H").Cells.SpecialCells(xlCellTypeLastCell).Row
    s3 = "https://maps.google.com/maps?q=" & Trim(Range("H" & CStr(i3)).Text)
    ActiveCell.Hyperlinks.Add Range("Q" & CStr(i3)), s3
    Range("Q4").Font.Color = RGB(248, 248, 248)
    Range("Q4").Font.Underline = False
    Range("Q4").Value = "GoogleMap"
Next i3

Open in new window

Question 2:

What do i need to add in the code to display the text "BLANK" if the hyperlink only contains this

"https://maps.google.com/maps?q="

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Troy Graham

ASKER

Hi Rgonzo1971,

Thanks for the fast reply. The code you sent through works ok with the exception of the entries that are blank.

The blank entries are showing up as "Click here" and hyperlink "https://maps.google.com/maps?q="

Furthermore if i wanted to
1. trim multiple columns what would the code look like?
2. trim multiple columns from a specific worksheet, what would the code look like.

Rgonzo1971

Could you send a dummy?

Rgonzo1971

A solution