Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I need some VBA code to select a user determined amount of rows in a list. Say the list is 50 records. The code must prompt the user for an amount of random records to select. If the user says 10, then the code must select 10 random records in the 50 records.

    Please help me to compile this code. TX

    This is not working:
    Sub RandomRows()
    Dim Rng As Range
    Dim NumRangeRows As Long
    Dim NumRandRows As Long
    NumRangeRows = Range("A1").CurrentRegion.Rows.Count
    NumRandRows = InputBox("Supply number of random rows to select")
    For i = 2 To NumRandRows
    RandRow = Round(Rnd() * NumRangeRows, 0)
    Set Rng = ActiveCell.Rows(RandRow)
    Next i
    Rng.Select
    End Sub
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Like this maybe?

    Code:
    Sub RandomRows()
        Dim lCt As Long
        Dim oRng As Range
        Dim oCell As Range
        lCt = InputBox("How many cells do you want selected?")
        If lCt > 0 Then
            Do
                Set oCell = Cells(Int(50 * Rnd() + 0.5), 1)
                If oRng Is Nothing Then
                    Set oRng = oCell
                    lCt = lCt - 1
                ElseIf Intersect(oRng, oCell) Is Nothing Then
                    Set oRng = Union(oRng, oCell)
                    lCt = lCt - 1
                End If
            Loop Until lCt = 0
            oRng.Select
        End If
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tx for the code Jan Karel.

    This is selecting cells in the A column. Is it possible to have the records rows selected? (Just asking )

    Appreciate the help.
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    oRng.EntireRow.Select

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Steve.

    This does select the entire row
    But I was actually wanting it to select the cells that make up the record. IE: The selection must not extend out of the lists width. The reason for asking is that I also would like the record highlighted before it gets copied to another sheet.

    Cheers!
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Something like this:
    Code:
    Sub RandomRows()
    	Dim lCt As Long
    	Dim oRng As Range
    	Dim oCell As Range
    	Dim rndRow As Integer 'new
    	lCt = InputBox("How many cells do you want selected?")
    	If lCt > 0 Then
     	Do
     	rndRow = Int(50 * Rnd() + 0.5) 'new
     	Set oCell = Range(Cells(rndRow, 1), Cells(rndRow, 5)) 'new 
     	If oRng Is Nothing Then
     	Set oRng = oCell
     	lCt = lCt - 1
     	ElseIf Intersect(oRng, oCell) Is Nothing Then
     	Set oRng = Union(oRng, oCell)
     	lCt = lCt - 1
     	End If
     	Loop Until lCt = 0
     	oRng.Select
    	End If
    End Sub
    I've added the comment of 'new to the lines I changed.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was receiving errors when the random number resulted in 0 and made a slight modification.

    Code:
    Sub RandomRows()
        Dim lCt As Long
        Dim oRng As Range
        Dim oCell As Range
        Dim rndRow As Integer
        lCt = InputBox("How many cells do you want selected?")
        
        If lCt > 0 Then
            Do
            rndRow = Int(50 * Rnd() + 0.5)
            If rndRow < 1 Then rndRow = 1
                Set oCell = Range(Cells(rndRow, 1), Cells(rndRow, 5))
                If oRng Is Nothing Then
                    Set oRng = oCell
                    lCt = lCt - 1
                ElseIf Intersect(oRng, oCell) Is Nothing Then
                    Set oRng = Union(oRng, oCell)
                    lCt = lCt - 1
                End If
            
            Loop Until lCt = 0
            oRng.Select
        End If
    End Sub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:

    Intersect(oRng.EntireRow, Range("A1").CurrentRegion).Select

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One thing to remember in VBA land is that random is not always random. I cannot explain it using the correct terminology, but I can give an example.

    Open the file, run the macro, and then change the background for the cells that were chosen.
    Save and exit the file.
    Open the file again run the macro (choose the same number of rows). The same rows are selected.

    If you choose 10 rows on the first running all 10 rows will be highlighted on the second, if you choose 9 rows the second time, 9 of 10 of the same will be chosen. 13 rows on the second running will have all 10 from the first running plus 3 more.

    I usually add a multiplier of some sort to the Rand. Something like:
    [s]rndRow = Int(50 * Rnd() * (Second(now()) /Minute(Now())) + 0.5)[/s]
    posted before testing - results can be greater than 50
    This on should work.
    rndRow = (Int(50 * Rnd() * (Second(Now()) / Minute(Now())) + 0.5) Mod 50) + 1

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry 'bout that. To ensure you do not get the same sequence time and again, start the routine with the
    Randomize
    statement.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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