Looking to expand a VBA Macro / Pick random cell from column

[peejaygee]

Hey All,

So, I have a list of items in a column, that I'd like to randomly pick from.

I have a very basic routine, that picks on, and highlights the column a based on what it picks. But,

I'd like to be able to randomly pick, but if it's already been picked before to not pick it again, I'm guessing by checking the colour of the cell?

At the moment, I have this

Sub FindRandomCell()
Dim ColumnA As Long
Dim StartRow As Long
Dim HeaderRow As Long
Dim LastRow As Long
Dim randomNum As Long
ColumnA = 1
HeaderRow = 1
StartRow = 3
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
randomNum = WorksheetFunction.RandBetween(StartRow, LastRow)
Cells(randomNum, ColumnA).Interior.Color = RGB(0, 255, 0)
Cells(randomNum, ColumnA).Select
End Sub

Any thoughts would be appreciated.

Thanks.

 

[peejaygee]

Nobody?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27Mar41
[COLOR="Navy"]Dim[/COLOR] Lastrow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] RandomNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
RandomNum = WorksheetFunction.RandBetween(3, Lastrow)
DoEvents
[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Cells(RandomNum, "A").Interior.Color = RGB(0, 255, 0)
    RandomNum = WorksheetFunction.RandBetween(3, Lastrow)
    c = c + 1
    [COLOR="Navy"]If[/COLOR] c > Lastrow [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Loop[/COLOR]
 
Cells(RandomNum, "A").Interior.Color = RGB(0, 255, 0)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[peejaygee]

Thank you, that did the trick.

I added in Cells(randomNum, "A").Select so it jumps to the cell and it's now perfect.

 

[MickG]

You're welcome