Choosing Random Cells

[cb12]

Good Evening,

I have a worksheet that at any one time can have anything from 500 to ~ 100,000 rows of data. In order to run some statistical tests on the data I like to take a snapshot of, for example, 5%. In one of the tests the snapshot is comprised of a random selection of data. In order to complete this I have a macro that assigns each row a random number and then skims the rows with the highest 5% of numbers.

I was wondering if anyone knew of (if such a thing exists) a better way to accomplish the same result.

Thank you in advance.

 

[mirabeau]

With 100k rows I would think your options for doing this other than with a macro are very limited.

If you want a somewhat faster macro than my above, that also retains the original ordering of the rows, then you can try the following code on the test data I gave.

It lists a random sample of 5% of the original rows on the same sheet, just to the right of the original set of rows.

Also, this assumes you are only interested in the values of the rows, and not the formatting or other properties. It can be modified, although would become considerably slower, if you also want to retain formats.

Sub randrows()
Dim a As Variant, u(), k As Long, i As Long
Dim lr As Long, lc As Integer, j As Integer
a = [a1].CurrentRegion
lr = UBound(a, 1): lc = UBound(a, 2)
ReDim u(1 To lr, 1 To lc)
For i = 1 To lr
If Rnd <= 0.05 Then
    k = k + 1
    For j = 1 To lc: u(k, j) = a(i, j): Next j
End If
Next i
Cells(1, lc + 3).Resize(k, lc) = u
End Sub

 

[mirabeau]

It can be modified, although would become considerably slower, if you also want to retain formats.

Actually, cancel that particular comment. It can be readily and just as rapidly done while still retaining formats. (I guess it's past coffee time).