Select Random Number of Rows in a Range

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

oRng.EntireRow.Select

Steve

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

How about:

Intersect(oRng.EntireRow, Range(“A1”).CurrentRegion).Select

Steve

Reply | Quote

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:
rndRow = Int(50 * Rnd() * (Second(now()) /Minute(Now())) + 0.5)
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

Reply | Quote

Sorry ’bout that. To ensure you do not get the same sequence time and again, start the routine with the
Randomize
statement.

Reply | Quote