Thread: Random row in sheet! (XL2003)

1. Random row in sheet! (XL2003)

Hi fellows!

Long time no see!

I've made this VERY important function that is able to select a random cell in a sheet between some_row and some_other_row - is'nt it just GREAT programming?:

Function GetRandomCellInColumn(lRow_First As Long, lRow_Last As Long, strCol As String, rgnActiveCell As Range) As String
Randomize
Dim strRandomCell As String
strRandomCell = ""
Dim lRandomRow As Long
lRandomRow = rgnActiveCell.Row
While (lRandomRow = rgnActiveCell.Row)
lRandomRow = Round(lRow_First + Rnd * (lRow_Last - lRow_First))
Wend
strRandomCell = strCol + CStr(lRandomRow)
GetRandomCellInColumn = strRandomCell
End Function

This call select a cell between F1 and F10:
MySheet.Range(GetRandomCellInColumn(1, 10, "F", ActiveCell)).Activate

Now my next project is to write a function that select a random COLUMN! I know that you people are just dying to see it!!! Ot what??

Sincerely,
M.

2. Re: Random row in sheet! (XL2003)

In your code, the probability that lRow_First or lRow_Last is selected is only half that for the rows in between, because of the way you use Round. To give each row the same probability, use

lRandomRow = Int(lRow_First + Rnd * (lRow_Last - lRow_First + 1))

What should happen if rgnActiveCell is not in the column specified by strCol? Should the return value still avoid the row of rgnActiveCell, or is it allowed to be in the same row then?

3. Re: Random row in sheet! (XL2003)

Yaa - OK Int(...) might be better. Thanx' for the input - and for the second part: It just selects a new cell - no big deal.

4. Re: Random row in sheet! (XL2003)

I think the following function is a bit more flexible - it returns a row number instead of a string; you can use this in several ways.

Function GetRandomRow( _
lRow_First As Long, _
lRow_Last As Long, _
lRowToAvoid As Long) As Long
Dim lRandomRow As Long
Randomize
Do
lRandomRow = Int(lRow_First + Rnd * (lRow_Last - lRow_First + 1))
Loop Until lRandomRow <> lRowToAvoid
GetRandomRow = lRandomRow
End Function

Example of usage:

ActiveSheet.Range("F" & GetRandomRow(10, 20, ActiveCell.Row)).Select

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•