Random rows....

  • Thread starter Thread starter Goran Stjepanovic
  • Start date Start date
G

Goran Stjepanovic

Hi,
Is there an automated way to pull 1000 random rows out of an
excel list of about 10000 rows of data? And then paste these rows into
another sheet.

Thanx
 
Here's a fairly rough way.

Sub test()
Const cSelection = 1000, cMax = 10000
Dim i As Long, rng As Range, rngDest As Range

Set rngDest = Sheet2.Cells(1, 1)

With ActiveSheet
.Columns(2).Insert
For i = 1 To cSelection
Do
Set rng = .Cells(Int((cMax * Rnd) + 1), 2)
Loop While Not IsEmpty(rng.Value)
rng.Value = 1
Next

.Rows(1).Insert
Range(.Columns(1), .Columns(2)).AutoFilter Field:=2, Criteria1:="1"
.Cells.SpecialCells(xlCellTypeVisible).Copy rngDest
.AutoFilterMode = False
.Rows(1).Delete
.Columns(2).Delete
End With
End Sub
 
Back
Top