I would say: maybe Solution 1 is working, but it's poor, sorry.
I'll try to explain why...
- Code has got no context
Range("A1").Select
Above code selects a cell A1 in currently active sheet!
Please, read this question: Dynamically Set Range in VBA for FillDown[^] and my answer
- Using
Select
is really bad programming practice, unless it is intended effect. - Unnecessary loops slow-down execution of code
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
I would suggest to use separate function as is i described in a referred thread.
- Bad way of
Rnd()
function usage Please, see here: Rnd function[^]
It would be something like that:
randomValue = Int((upperbound - lowerbound + 1) * Rnd()) + lowerbound
Finally, i would do "lottery system" in that way:
Function GetFirstEmptyRow(wsh As Worksheet, Optional sColName as String= "A") As Long
GetFirstEmptyRow = wsh.Range(sColName & wsh.Rows.Count).End(xlUp).Row +1
End Function
Sub RandomWinner()
Dim firstrow As Long, lastrow As Long, winner As Long
Dim dstwsh As Worksheet
Set dstwsh = Thisworkbook.Worksheets("Sheet1")
firstrow = 2
lastrow = GetFirstEmptyRow(dstwsh) -1
winner = Int((lastrow - firstrow + 1) * Rnd() + firstrow)
dstwsh.Range("A" & winner).Select
Set dstwsh = Nothing
End Sub
Note: it would be good to add
error handler[
^] ;)