This avoids the duplicate number returned by the standard Rnd function.
These two procedures should be copied to the standard code module1. The function should be put at the top of the code module. The second procedure calls the function and requires three long integer data type numbers in the order of sample size, lower boundry of universe and upper boundry of universe. In the myArr procedure, the numbers 25, 1 and 100 were used to get a return of 25 row numbers from rows 1 through 100 of sheet 2. You will probably need to change to 1 and 100 to the actual universe of rows that you want to sample. Run the myArr procedure to obtain results.
Code:
Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
UniqueRandomNumbers = False
If NumCount < 1 Then Exit Function
If LLimit > ULimit Then Exit Function
If NumCount > (ULimit - LLimit + 1) Then Exit Function
Set RandColl = New Collection
Randomize
Do
On Error Resume Next
i = CLng(Rnd * (ULimit - LLimit) + LLimit)
RandColl.Add i, CStr(i)
On Error GoTo 0
Loop Until RandColl.Count = NumCount
ReDim varTemp(1 To NumCount)
For i = 1 To NumCount
varTemp(i) = RandColl(i)
Next i
Set RandColl = Nothing
UniqueRandomNumbers = varTemp
Erase varTemp
End Function
Code:
Code:
Sub myArr()
Dim lr As Long, rwNbr As Variant
rwNbr = UniqueRandomNumbers(25, 1, 100)
For i = 1 To 25
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Rows(rwNbr(i)).Copy Sheets("Sheet1").Range("A" & lr + 1)
Next
End Sub
Code: