I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's  [Please notice that this page is provided just for historical purposes. You might find it an interesting example for conditional compiling. I suggest to use my UDF sbRandInt now, but if you only need positive random integers 1..n UniqRandInt still is the best fit.] "Few things are harder to put up with than the annoyance of a good example." [Mark Twain] Sometimes you need to create random integers without repetition. If you need 20 random integers within range 1 .. 100, select 20 adjacent cells, enter =UniqRandInt(100) as array formula. If you need them in range 100 .. 199, enter =UniqRandInt(100)+99, in general:=UniqRandInt(Endvalue - Startvalue + 1) + Startvalue - 1 If your random integers may occur up to 6 times, use =UniqRandInt(100,6). Please notice that the constant ALLOW_REPETITION has to be set to True. If you need this feature in VBA programming environment then use VBUniqRandInt() as shown below. Example: Select cells A1:B3, type =UniqRandInt(6) and enter with CTRL + SHIFT + ENTER (as array formula).
 Option Explicit'If lRange >> n then set LATE_INITIALISATION to true'For example if lRange=1000000 and if 1000 cells are selected (n=1000)#Const LATE_INITIALISATION = False'If random integers may occur more than once, allow repetitions#Const ALLOW_REPETITION = True#If ALLOW_REPETITION ThenFunction UniqRandInt(ByVal lRange As Long, _   Optional lMaxOccurence As Long = 1) As Variant#ElseFunction UniqRandInt(ByVal lRange As Long) As Variant#End If'Returns n unique (=non-repeating) random integers within 1..lRange,'lRange >= n if n cells in a worksheet have been selected and the'function has been entered as array formula (CTRL+SHIFT+ENTER).'Set ALLOW_REPETITION = True and call with lMaxOccurences > 1 if'random integers may occur more than once.'Algorithm by: sulprobil http://Reverse("moc.liborplus.www") V1.02Dim vA As VariantDim vR As VariantDim i As LongDim lr As LongDim lrow As LongDim lcol As LongApplication.VolatileIf TypeName(Application.Caller) <> "Range" Then   UniqRandInt = CVErr(xlErrRef)   Exit FunctionEnd If#If ALLOW_REPETITION Then   If lMaxOccurence < 1 Then       UniqRandInt = CVErr(xlErrNum)       Exit Function   End If   lRange = lRange * lMaxOccurence#End IfIf Application.Caller.Count > lRange Then   UniqRandInt = CVErr(xlErrValue)   Exit FunctionEnd IfReDim vR(1 To Application.Caller.Rows.Count, _               1 To Application.Caller.Columns.Count)ReDim vA(1 To lRange)#If Not LATE_INITIALISATION Then   For i = 1 To lRange       #If ALLOW_REPETITION Then           vA(i) = Int((i - 1) / lMaxOccurence) + 1       #Else           vA(i) = i       #End If   Next i#End Ifi = 1For lrow = 1 To UBound(vR, 1)   For lcol = 1 To UBound(vR, 2)       lr = Int(((lRange - i + 1) * Rnd) + 1)       #If LATE_INITIALISATION Then           If vA(lr) = 0 Then               #If ALLOW_REPETITION Then                   vR(lrow, lcol) = Int((lr - 1) / _                        lMaxOccurence) + 1               #Else                   vR(lrow, lcol) = lr               #End If           Else       #End If           vR(lrow, lcol) = vA(lr)       #If LATE_INITIALISATION Then           End If           If vA(lRange - i + 1) = 0 Then               #If ALLOW_REPETITION Then                   vA(lr) = Int((lRange - i + 1 - 1) / _                        lMaxOccurence) + 1               #Else                   vA(lr) = lRange - i + 1               #End If           Else       #End If               vA(lr) = vA(lRange - i + 1)       #If LATE_INITIALISATION Then           End If       #End If       i = i + 1   Next lcolNext lrowUniqRandInt = vREnd Function#If ALLOW_REPETITION ThenFunction VBUniqRandInt(lCount As Long, _            ByVal lRange As Long, _            Optional lMaxOccurence As Long = 1) As Variant#ElseFunction VBUniqRandInt(lCount As Long, _            ByVal lRange As Long) As Variant#End If'Returns lCount unique (=non-repeating) random integers within'1..lRange. lRange has to be >= lCount.'Set ALLOW_REPETITION = True and call with lMaxOccurences > 1 if'random integers may occur more than once.'Algorithm by: sulprobil http://Reverse("moc.liborplus.www") V1.02Dim vA As VariantDim vR As VariantDim i As LongDim lr As LongApplication.Volatile#If ALLOW_REPETITION Then   If lMaxOccurence < 1 Then       VBUniqRandInt = CVErr(xlErrNum)       Exit Function   End If   lRange = lRange * lMaxOccurence#End IfIf lCount > lRange Then   VBUniqRandInt = CVErr(xlErrValue)   Exit FunctionEnd IfReDim vR(1 To lCount)ReDim vA(1 To lRange)#If Not LATE_INITIALISATION Then   For i = 1 To lRange       #If ALLOW_REPETITION Then           vA(i) = Int((i - 1) / lMaxOccurence) + 1       #Else           vA(i) = i       #End If   Next i#End IfFor i = 1 To lCount   lr = Int(((lRange - i + 1) * Rnd) + 1)   #If LATE_INITIALISATION Then       If vA(lr) = 0 Then           #If ALLOW_REPETITION Then               vR(i) = Int((lr - 1) / lMaxOccurence) + 1           #Else               vR(i) = lr           #End If       Else   #End If       vR(i) = vA(lr)   #If LATE_INITIALISATION Then       End If       If vA(lRange - i + 1) = 0 Then           #If ALLOW_REPETITION Then               vA(lr) = Int((lRange - i + 1 - 1) / _                    lMaxOccurence) + 1           #Else               vA(lr) = lRange - i + 1           #End If       Else   #End If           vA(lr) = vA(lRange - i + 1)   #If LATE_INITIALISATION Then       End If   #End IfNext iVBUniqRandInt = vREnd Function