Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •