Selecting random rows
 

Hi,

Is it possible to select, say 70 random rows from 500 rows? Sorry it's
a very short question, but I don't even know where to start!

Many thanks,

Anar


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=509941

Selecting random rows
 

Hi Anar,

Here is one way

Dim rng As Range
Do
If rng Is Nothing Then
Set rng = Rows(Fix(Rnd() * 500 + 1))
Else
Set rng = Union(rng, Rows(Fix(Rnd() * 500 + 1)))
End If
Loop Until rng.Areas.Count = 70

rng.Select


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"anar_baku" wrote
in message ...

Hi,

Is it possible to select, say 70 random rows from 500 rows? Sorry it's
a very short question, but I don't even know where to start!

Many thanks,

Anar


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile:
http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=509941

Selecting random rows
 

This is a bit better as it is relative to your target area

Dim rng As Range
Dim iRow As Long
With Rows("10:510")
Do
iRow = Fix(Rnd() * 500 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count = 70
End With

rng.Select


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"anar_baku" wrote
in message ...

Hi,

Is it possible to select, say 70 random rows from 500 rows? Sorry it's
a very short question, but I don't even know where to start!

Many thanks,

Anar


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile:
http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=509941

Selecting random rows
 

Bob,

no guarantee that it wont select two subsequent rows,
where a union would merge the areas.

?union([1:1],[2:2]).areas.Count
1

Alternative:

Sub RandomRows()
Dim d As Object, r As Range, vKeys, x&

'get a set of 70 unique numbers
Set d = CreateObject("Scripting.Dictionary")
While d.Count < 70
'Define the min,max of your numbers
x = RndBetween(10, 510)
If Not d.Exists(x) Then d.Add x,Empty
Wend

'Create a multiarea range
vKeys = d.keys
Set r = Rows(vKeys(0))
For x = 1 To UBound(vKeys)
Set r = Union(r, Rows(vKeys(x)))
Next
'Select it
r.Select

End Sub

Function RndBetween(low&, high&) As Long
RndBetween = CLng(Rnd * (high - low)) + low
End Function








--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

This is a bit better as it is relative to your target area

Dim rng As Range
Dim iRow As Long
With Rows("10:510")
Do
iRow = Fix(Rnd() * 500 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count = 70
End With

rng.Select

Selecting random rows
 

Good point. I think I thought about that at the start, but obviously lost
the thought when doing it <G

Thanks

"keepITcool" wrote in message
.com...

Bob,

no guarantee that it wont select two subsequent rows,
where a union would merge the areas.

?union([1:1],[2:2]).areas.Count
1

Alternative:

Sub RandomRows()
Dim d As Object, r As Range, vKeys, x&

'get a set of 70 unique numbers
Set d = CreateObject("Scripting.Dictionary")
While d.Count < 70
'Define the min,max of your numbers
x = RndBetween(10, 510)
If Not d.Exists(x) Then d.Add x,Empty
Wend

'Create a multiarea range
vKeys = d.keys
Set r = Rows(vKeys(0))
For x = 1 To UBound(vKeys)
Set r = Union(r, Rows(vKeys(x)))
Next
'Select it
r.Select

End Sub

Function RndBetween(low&, high&) As Long
RndBetween = CLng(Rnd * (high - low)) + low
End Function








--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

This is a bit better as it is relative to your target area

Dim rng As Range
Dim iRow As Long
With Rows("10:510")
Do
iRow = Fix(Rnd() * 500 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count = 70
End With

rng.Select