How to Set Range to 50 Random nonsequencial rows?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have a spreadsheet with 4000 rows. I choose 50 of those rows using the excel
random number generator as in:

LastRow = Sheets(OS).Cells(Rows.Count, "a").End(xlUp).Row
LastCol = Sheets(OS).Range("A2").End(xlToRight).Column
N= LastRow
For lj = 1 To 50
ll = Int(N * Rnd) + 1
lRndRows(lj) = ll
Next lj

How would I set the range as in:

Dim rng As Range
Dim myCell As Range
Dim curwk As Worksheet
OS = ActiveSheet.Name
Set curwk = Sheets(OS)
With Curwk
Set rng=?????
end with

to those 50 rows?

Thanks for any help

Dennis
 
Your first routine pick a set of rows at random and stores them in a array.
You need to make a range out of this set:


Sub routine()
Dim lRndRows(51)
OS = "OS"
LastRow = Sheets(OS).Cells(Rows.Count, "a").End(xlUp).Row
LastCol = Sheets(OS).Range("A2").End(xlToRight).Column
N = LastRow
For lj = 1 To 50
ll = Int(N * Rnd) + 1
lRndRows(lj) = ll
Next lj
Dim rng As Range
Set rng = Range("A" & lRndRows(1)).EntireRow
For i = 2 To 50
Set rng = Union(rng, Range("A" & lRndRows(i)).EntireRow)
Next
MsgBox (rng.Address)
rng.Select
End Sub
 
Actually I think I found an easier way.

Dim rng As Range
Dim myCell As Range
Dim curwk As Worksheet
dim OS as string

OS = ActiveSheet.Name
Set curwk = Sheets(OS)
With Curwk
Set rng = Nothing
For lj = 1 To 50
ll = Int(N * Rnd) + 1
Set rng = rng + .Rows(ll)
Next lj
end with
 
Actually I think I found an easier way.

Dim rng As Range
Dim myCell As Range
Dim curwk As Worksheet
dim OS as string

OS = ActiveSheet.Name
Set curwk = Sheets(OS)
With Curwk
Set rng = Nothing
For lj = 1 To 50
ll = Int(N * Rnd) + 1
Set rng = rng + .Rows(ll)
Next lj
end with
 
Actually I think I found an easier way.

Dim rng As Range
Dim myCell As Range
Dim curwk As Worksheet
dim OS as string

OS = ActiveSheet.Name
Set curwk = Sheets(OS)
With Curwk
Set rng = Nothing
For lj = 1 To 50
ll = Int(N * Rnd) + 1
Set rng = rng + .Rows(ll)
Next lj
end with
 
Thanks Bernd!!!

I didn't think of that. If you ask for 50 random numbers to represent 50 unique
random rows, sometimes some of the 50 random rows will be duplicates. Thus one
has to ask for 50 unique random rows. Your code was very helpful.

Dennis
 
Back
Top