random selection from a growing range

[Watchdawg]

Good morning, guru's! OK, I have a workbook that has a list of items in columns B-E (irrelevant to this discussion I believe). Each of those lines is listed with a sequential number in column "A". The list can grow and shrink daily (today might have 124 lines, tomorrow 250 and the next day 45). I need a command button that will pick a random number from all populated cells in column A, then highlight and select that entire row.
Everything I've found so far required me to put in a start/stop value, or was trying to create random numbers to place into a range, that's not exactly what I'm looking for.

Anyone here that can help? I've played with RandBetween with no success

Thanks in advance!

[Paul_Hossler]

Should get you started



Option Explicit


Private Sub CommandButton1_Click()
Dim r As Range
Dim rowLast As Long, rowFirst As Long, rowPicked As Long

Set r = Me.Cells(1, 1).CurrentRegion

rowFirst = 2
rowLast = r.Rows.Count

Randomize

rowPicked = Application.WorksheetFunction.RandBetween(rowFirst, rowLast)


r.Interior.ColorIndex = xlColorIndexNone

r.Rows(rowPicked).Interior.ColorIndex = 15
End Sub

[Watchdawg]

Perfect! Thanks so much for the help Paul! :bow:

[snb]

Sub M_snb()
Randomize
Rows(Rnd * UsedRange.Rows.Count).Select
End Sub