'rather slow for large numbers
Option Explicit
Sub generate_rnd(LowNr As Long, DiffNr As Long, Cnt As Long)
Dim WB As Workbook
Dim arr As Variant
If Cnt > DiffNr Then
MsgBox "Cnt > DiffNr", 48, "ERROR"
Exit Sub
End If
If DiffNr > Rows.Count Then
MsgBox "DiffNr > " & Rows.Count, 48, "ERROR"
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Add
Set WB = ActiveWorkbook
With Range("A" & LowNr + 1 & ":A" & LowNr + Cnt)
.Cells(1, 1).FormulaArray = _
"=SMALL(IF(COUNTIF(R" & LowNr & "C1:R[-1]C,ROW(R" & LowNr & ":R" & DiffNr + LowNr - 1 & "))<>1," & _
"ROW(R" & LowNr & ":R" & DiffNr + LowNr - 1 & ")),1+INT(RAND()*(" & DiffNr & "-ROW()+ROW(R" & LowNr + 1 & "C1))))"
.Cells(1, 1).Copy .Offset(1, 0).Resize(Cnt, 1)
arr = .Value
End With
WB.Close False
ActiveSheet.Cells(1, 1).Resize(Cnt, 1) = arr
Application.ScreenUpdating = True
End Sub
Sub test()
'arguments
'lowest number
'"range": added to lowest number gives highest number
'how many numbers to generate
Call generate_rnd(10, 500, 30)
End Sub