VBA - Find Hyperlinks in column from a specific domain and open in browser

[djgenesis13]

Hello Everybody,

I need your help guys.

I am trying to find hyperlinks in a column and open them in my default browser.

I am currently using this:

Sub openlink1()


'Open Links
    
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    On Error Resume Next
    Set Sh = Worksheets("New")
    With Sh
        Set Rng = .Range("g2:g50" & .Cells(.Rows.Count, "g").End(xlUp).Row)
    End With
    For Each Cell In Rng
        ThisWorkbook.FollowHyperlink Cell.Value, NewWindow:=True
    Next Cell


End Sub

This works great BTW if you want to open ALL links in the range you set even if there are blank cells.

My problem is that in this range there are multiple links from various domains.

I want a VBA script that will search a range and open the hyperlinks that start with "http://www.example.com" and NOT ALL the links from that range.



Thanks alot guys.

Vasilis

 

[KeepTrying]

Dear Vasilis,

Try this code:

Sub openlink2()


'Open Links


Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim SearchThisSite As String


'On Error Resume Next


SearchThisSite = "http://www.example.com"
Set Sh = Worksheets("New")
With Sh
    Set Rng = .Range("g2:g50" & .Cells(.Rows.Count, "g").End(xlUp).Row)
End With


For Each Cell In Rng
    If Cell.Value Like SearchThisSite & "*" Then ThisWorkbook.FollowHyperlink Cell.Value, NewWindow:=True
Next Cell


'On Error GoTo 0


End Sub

 

[djgenesis13]

KeepTrying you are the best. It works great!!!!! THANK YOU!!!!

One more question. I can see that for the range, if I set the second value to more than 1000, it doesnt run the script at all. For example if I set it to "g2:g1000" it works. If I set it to "g2:g2000" or "g2:g3000" or "g2:g4000" etc, it doesnt run the script. How can I set a wider range so that if the spreadsheet goes beyond line "1000" (which it will at some point in the near future). Is this problem my workbook's or its an excel/vba setting?

Thanks again

 

[KeepTrying]

You're welcome

I think the issue is the following line which is not quite clear to me:

Set Rng = .Range("g2:g1000" & .Cells(.Rows.Count, "g").End(xlUp).Row)

See revised code where I simply used:

Set Rng = .Range("g2:g4000")

Sub openlink3()


'Open Links


Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim SearchThisSite As String


'On Error Resume Next


SearchThisSite = "http://www.example.com"
Set Sh = Worksheets("New")
With Sh
    Set Rng = .Range("g2:g4000")
End With


For Each Cell In Rng
    If Cell.Value Like SearchThisSite & "*" Then ThisWorkbook.FollowHyperlink Cell.Value, NewWindow:=True
Next Cell


'On Error GoTo 0


End Sub

 

[djgenesis13]

Man you simple rock.

Sometimes something that simple can make such a difference.

Problem solved!

Thanks a lot (once again)

 

[KeepTrying]

I'm glad I could help and fully agree with you
Have a nice day.