activate hyperlinks VBA

[Leske]

Hello

I have a link in excel but it not activate.

So for the moment i press F2 and hit enter that way the hyperlinks works.

Now i want to do this with a Macro:

Dim LastRow As Long, i As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
'
If Cells(i, 2).Value <> "" Then
 
    [COLOR=#ff0000]ActiveCell.Hyperlinks
[/COLOR]    
   
End If
Next i
End Sub

But don't know the code for just activate it so if anyone can help me with the red part that would be great.

Kind regards,

Lesly

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim LastRow As Long, i As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        With Cells(i, 2)
            If .Value <> "" Then
                ActiveSheet.Hyperlinks.Add Anchor:=.Cells(1, 1), Address:=.Value
            End If
        End With
    Next i
End Sub

 

[Leske]

Yes it works but i have a few questions why do you use with cells(i,2)?

And how does the code of hyperlinks work? what does Anchor means and why do you use .cells(1,1)

Kind regards,

thx for your help

 

[Andrew Poulsom]

I used a With ... End With construct to avoid repeating the reference. Without it the code would be:

Sub Test()
    Dim LastRow As Long, i As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Cells(i, 2).Value <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=Cells(i, 2).Value
        End If
    Next i
End Sub

Anchor is the cell that contains the Hyperlink.