I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

If you want to generate non-repeating random integers between two given values I suggest to use my UDF RandInt:

You can also generate random integers which may appear repeatedly up to lRept times:

Function RandInt(lMin As Long, _
    lMax As Long, _
    Optional ByVal lCount As Long = 0, _
    Optional lRept As Long = 1) As Variant
'Returns lCount random integers between lMin and lMax, each one
'occurring zero to lRept times. lMax - lMin + 1 must be greater
'or equal to lCount. If called from worksheet and lCount = 0
'then number of selected cells specify lCount.
'Error values:
'#NUM!   - lRept is less than 1
'#REF!   - lCount is greater than (lMax - lMin + 1) * lRept
'#VALUE! - lCount is less than 1
'Reverse("moc.LiborPlus.www") PB V0.31 30-Oct-2009

Dim i As Long, j As Long
Dim lRnd As Long, lRange As Long
Dim lRow As Long, lCol As Long
Const CLateInitFactor = 50

If lRept < 1 Then
    RandInt = CVErr(xlErrNum)
    Exit Function
End If

lRange = (lMax - lMin + 1) * lRept

With Application.Caller
    If TypeName(Application.Caller) = "Range" And lCount = 0 Then
        lCount = .Count
        If lCount > lRange Then
            RandInt = CVErr(xlErrRef)
            Exit Function
        End If
        ReDim lR(1 To .Rows.Count, 1 To .Columns.Count) As Long
    ElseIf lCount < 1 Then
        RandInt = CVErr(xlErrValue)
        Exit Function
    ElseIf lCount > lRange Then
        RandInt = CVErr(xlErrRef)
        Exit Function
    Else
        'If you know an elegant way to avoid 2nd dimension, let me know
        ReDim lR(1 To lCount, 1 To 1) As Long
    End If
End With

'Randomize            'Uncomment if you like to
'Application.Volatile 'If you need this function to be volatile

ReDim lT(1 To lRange) As Long
'If we have a huge range of possible random integers and a comparably
'small number of draws, i.e. if (lMax - lMin) * lRept >> lCount
'then we can save some runtime with late initialization.
If lRange / lCount < CLateInitFactor Then
    For i = 1 To lRange
        lT(i) = Int((i - 1) / lRept) + lMin
    Next i
End If

i = 1
If lRange / lCount < CLateInitFactor Then
    For lRow = 1 To UBound(lR, 1)
        For lCol = 1 To UBound(lR, 2)
            lRnd = Int(((lRange - i + 1) * Rnd) + 1)
            lR(lRow, lCol) = lT(lRnd)
            lT(lRnd) = lT(lRange - i + 1)
            i = i + 1
        Next lCol
    Next lRow
Else
    j = lMin: If lMin <= 0 And lMax >= 0 Then j = 1
    For lRow = 1 To UBound(lR, 1)
        For lCol = 1 To UBound(lR, 2)
            lRnd = Int(((lRange - i + 1) * Rnd) + 1)
            If lT(lRnd) = 0 Then
                lR(lRow, lCol) = Int((lRnd - 1) / lRept) + j
            Else
                lR(lRow, lCol) = lT(lRnd)
            End If
            If lT(lRange - i + 1) = 0 Then
                lT(lRnd) = Int((lRange - i) / lRept) + j
            Else
                lT(lRnd) = lT(lRange - i + 1)
            End If
            i = i + 1
        Next lCol
    Next lRow
    'If lRange includes zero we need to shift result array
    If lMin <= 0 And lMax >= 0 Then
        For lRow = 1 To UBound(lR, 1)
            For lCol = 1 To UBound(lR, 2)
                lR(lRow, lCol) = lR(lRow, lCol) + lMin - 1
            Next lCol
        Next lRow
    End If
End If

RandInt = lR

End Function