Hi there,


Can any body help me in creating a macro that could select 20 row
randomly from a list of 200 rows and copy those to sheet2, but th
copied rows must not be repeated and must be 20. for example

COLUMN A COLUMN B
1 cat
2 dog
3 cup
4 hat
5 chair

Now on the basis of above data the program should select both th
columns A and B and give them a random sort and select 3 differen
records and paste those to sheet2, For example the desired result ma
be:

1 cat
5 chair
3 cup


Regards,



Darn

--
Message posted from http://www.ExcelForum.com

Darno,
does this give you a start ?
i use the collection's key to ensure uniqueness

Sub ExtractRandom20()
Dim tmp As Collection
Dim rngSrc As Range
Dim rngTgt As Range
Dim r&


Set rngSrc = Worksheets(1).Range("a1:a200")
Set rngTgt = Worksheets(2).Range("a1:a20")

If rngTgt.Rows.Count 0.5 * rngSrc.Rows.Count Then
MsgBox "Make target range smaller"
Exit Sub
End If

Randomize

Set tmp = New Collection
On Error Resume Next
With rngSrc
While tmp.Count < rngTgt.Rows.Count
r = Int(Rnd * .Rows.Count + 1)
tmp.Add .Cells(r, 1).Value, CStr(r)
Wend
End With

For r = 1 To tmp.Count
rngTgt(r, 1) = tmp(r)
Next

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


darno wrote:

Hi there,


Can any body help me in creating a macro that could select 20 rows
randomly from a list of 200 rows and copy those to sheet2, but the
copied rows must not be repeated and must be 20. for example

COLUMN A COLUMN B
1 cat
2 dog
3 cup
4 hat
5 chair

Now on the basis of above data the program should select both the
columns A and B and give them a random sort and select 3 different
records and paste those to sheet2, For example the desired result may
be:

1 cat
5 chair
3 cup


Regards,



Darno


---
Message posted from http://www.ExcelForum.com/