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

peejaygee

New Member
Joined
Jul 23, 2006
Messages
40
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.
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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

New Member
Joined
Jul 23, 2006
Messages
40
Thank you, that did the trick.

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

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top