Randomly select from long list

**Lifeseeker** · 11-17-2011, 05:39 PM

Hi,

I have a long list of rows, and what I would like to is to randomly select 60 rows from the long list with a click of a button.

I'm not sure what the best approach would be.

Can anybody shed some light on this please?

Please see the attached.

Thank you

**tigeravatar** · 11-17-2011, 06:11 PM

Lifeseeker,

Attached is a modified version of your example workbook. I copied the list of IDs to Sheet2, column A. In Sheet2, column B I then entered the formula =RAND(). Then I set Sheet2 to hidden.

Here's the macro assigned to the button:

Private Sub CommandButton1_Click()
    
    Randomize
    Sheets("Sheet2").Calculate
    Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns("A:B")).Sort Sheets("Sheet2").Range("B1"), xlAscending, , , , , , xlNo
    ActiveSheet.Range("D4:D63").Value = Sheets("Sheet2").Range("A1:A60").Value
    
End Sub

**Lifeseeker** · 11-17-2011, 06:24 PM

Oh wow. Thank you

Could you explain this part please?

Randomize
    Sheets("Sheet2").Calculate
    Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns("A:B")).Sort Sheets("Sheet2").Range("B1"), xlAscending, , , , , , xlNo

Does "randomize" do the randomizing by default? The Intersect() is looking for the intersection between the used range and A and B in Sheet2?

**tigeravatar** · 11-17-2011, 06:30 PM

Randomize shuffles Excel's randomization functions to start at a new location. After that, the .Calculate is what's causing the =RAND() functions to pull new random numbers, based on the current location of Excel's randomization table. In Excel's help file [F1], the Randomize Statement is defined as: "Intitializes the random-number generator."

the Intersect() method catches all items in columns A and B that are in sheet 2's used range. Note that this isn't strictly necessary because the only items in sheet2's used range are columns A and B. If you ever updated sheet2 with extra info in the other columns though, it wouldn't upset the macro because its using intersect.

**Lifeseeker** · 11-17-2011, 06:50 PM

THank you.

I would like to put the result to a new workbook or a new sheet within the active workbook. How would I do so?

**tigeravatar** · 11-17-2011, 06:56 PM

To a new sheet in the same workbook:

Private Sub CommandButton1_Click()
    
    Randomize
    Sheets("Sheet2").Calculate
    Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns("A:B")).Sort Sheets("Sheet2").Range("B1"), xlAscending, , , , , , xlNo
    Sheets.Add(After:=Sheets(Sheets.Count)).Range("A1:A60").Value = Sheets("Sheet2").Range("A1:A60").Value
    
End Sub

To a new workbook:

Private Sub CommandButton1_Click()
    
    Randomize
    Sheets("Sheet2").Calculate
    Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns("A:B")).Sort Sheets("Sheet2").Range("B1"), xlAscending, , , , , , xlNo
    With Sheets.Add(After:=Sheets(Sheets.Count))
        .Range("A1:A60").Value = Sheets("Sheet2").Range("A1:A60").Value
        .Move
    End With
    
End Sub