Selecting random rows

[anar_baku]

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

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[keepITcool]

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

[Bob Phillips]

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

Thanks

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> 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