Random numbers generation

[Nishant]

Hello,
I am new to VBA scripting, I have a problem that in an excel sheet i
have got a large number of cusips in a column. I want to pull cusips
randomly from that list. I am currently doing a RANDBETWEEN after
calculating the number of rows and getting to the row no that comes in
the random number.the problem is that everday the no of rows keep on
changing. I want to write a macro for the same .
Thanks in advance.
Nishant

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range, num as Long
set rng = Range(cells(2,5),cells(2,5).End(xldown))
num = int(rnd()*rng.count+1)
set rng1 = rng(num)
msgbox rng1.value

 

[Nishant Nayar]

hi,
I tried running the script but got the error message

Sub Macro1()

Macro1 Macro

Dim rng as Range, rng1 as Range, num as Long
set rng = Range(cells(2,5),cells(2,5).End(xldown))
num = int(rnd()*rng.count+1)
set rng1 = rng(num)
msgbox rng1.value

End Sub