Selecting certain random rows?

[jbritt11]

Hi, I want to automatically select an array of random rows and then extract them to a new sheet. For example how do I randomly select row 1,23,76,234 and extract them to a new sheet?

1

.

.

23

<tbody>
</tbody>

ect.

 

[Chris Macro]

Try this guy out:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CopyRandomRows()<br><br><SPAN style="color:#007F00">'Create Random selection</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> Rng = ActiveSheet.UsedRange<br>  row_count = Rng.Rows.Count<br>  Amt_copied = 4<br>  dupTest = <SPAN style="color:#00007F">True</SPAN><br>  <br>  <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 4<br>    <br>    <SPAN style="color:#007F00">'Make sure row has not already been randomly chosen</SPAN><br>      <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> dupTest = <SPAN style="color:#00007F">True</SPAN><br>        rand_row = Application.WorksheetFunction.RandBetween(1, row_count)<br>        <SPAN style="color:#007F00">'rand_row = 5</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> InStr(1, rng_str, rand_row & ":") = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>          dupTest = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>      <br>      <SPAN style="color:#00007F">Loop</SPAN><br>    <br>    rng_str = rng_str & rand_row & ":" & rand_row & ","<br>  <br>    dupTest = <SPAN style="color:#00007F">True</SPAN><br>  <br>  <SPAN style="color:#00007F">Next</SPAN> x<br><br><SPAN style="color:#007F00">'Get rid of last comma</SPAN><br>  rng_str = Left(rng_str, Len(rng_str) - 1)<br><br><SPAN style="color:#007F00">'Copy Random rows</SPAN><br>    ActiveSheet.Range(rng_str).Copy<br><br><SPAN style="color:#007F00">'Paste Random rows</SPAN><br>    Sheet2.Rows("1:1").PasteSpecial<br><br><SPAN style="color:#007F00">'Clear Clipboard</SPAN><br>  CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mirabeau]

Hi, I want to automatically select an array of random rows and then extract them to a new sheet. For example how do I randomly select row 1,23,76,234 and extract them to a new sheet?

1

.

.

23

<tbody>
</tbody>

ect.

try

Sub test()

Dim b() As Boolean, ash
Dim rws As Long, c As Long, x As Long
Set ash = ActiveSheet

Sheets.Add.Name = "new"
ash.Activate

With Range("A1").CurrentRegion
rws = .Rows.Count: If rws < 4 Then Exit Sub
ReDim b(1 To rws)
Do
    x = Int(Rnd * rws) + 1
    If Not b(x) Then
        c = c + 1
        .Rows(x).Copy Sheets("new").Cells(c, 1)
        b(x) = True
    End If
Loop Until c = 4
End With

End Sub