How to Set Range to 50 Random nonsequencial rows?

[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

 

[Guest]

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

 

[Dennis]

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

 

[Dennis]

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

 

[Dennis]

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

 

[Bernd]

Hello,

You know that sometimes you will end up with less than 50 selected
rows?

If you need 50 different rows:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

 

[Dennis]

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