Bootstrapping-Random sampling with replacement

[RINCONPAUL]

On sheet '40%' of the attachment I want to place a macro trigger button. This trigger would action the following:
1/ Sort all the data rows in a random order (apart from rows 1 & 2 )
2/ Count the number of randomly sorted rows and calculate 60% of the row count. In the attachment it would be 60% of 28 rows = 17 rows (rounded)
3/ Copy the first 17 rows and paste into Sheet1
4/End of macro.

Now I can record this but have come up with two problems that my limited knowledge of vba can't solve?
1/The total number of rows on the '40%' worksheet varies, so the reference needs to be dynamic.
2/ I've had no success in randomly mixing up the data with a trigger.

The concept is to take a set of historical data, take a 60% sample of it all mixed up and not in time order, this sample then goes to a pivot table for further analysis, but along with other runs of the same sampling, to generate an unbiased total sample, for analysis going forward.

Cheers and Happy Holidays to all.

[snb]

This will do:


Sub M_snb()
With Sheet1.Cells(1).CurrentRegion
sn = .Offset(2).Resize(.Rows.Count - 2)
End With

With Sheet1.Cells(1, 50).Resize(UBound(sn))
.Value = "=rand()"
Sheet2.Cells(30, 1).Resize(Round(UBound(sn) * 0.6, 0), UBound(sn, 2)) = Application.Index(sn, Application.Rank(.Offset, .Offset), [transpose(row(1:20))])
End With
End Sub

NB. Delete column U in sheet "40%" first.

[RINCONPAUL]

Wow, great work snb! Just a few lines of code and you'll save me so much time. Thankyou so much.:hi:

[snb]

I hope you'll analyse the code so you can explain to any other what every element does.