Open random URL from a range

Question

I would need to open a random selected link from a cell to a website in my list. My internet links are put in column B, but I cannot get it to open the hyperlink after it has selected a cell from my list.

Sub Test()  
Dim Sh As Worksheet  
Dim Rng As Range 
Dim Cell As Range 

Set Sh = Worksheets("Sheet1") 

With Sh 
    Set Rng = .Range("C1:C" & .Cells(.Rows.Count, "C").End(xlUp).Row) 
End With 

For Each Cell In Rng
    ThisWorkbook.FollowHyperlink Cell.Value 
Next Cell  

End Sub

Answer 1

Just to be sure I got it right:

• The addresses are stored in column B (in your code you're using column C)
• You don't want to open all of the addresses, but only one random one?
• The addresses are full URLs? (Like "https://stackoverflow.com/")

I tidied up your code a little bit. Read the comments, to understand what is happening.

Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range

Set Sh = Worksheets(1)

' Get a Range object of all possible addresses
Set Rng = Intersect(Sh.UsedRange, Sh.Range("B:B"))

' Open one random element of them
ThisWorkbook.FollowHyperlink Rng(Int(Rnd * Rng.Count) + 1)
End Sub