Random Numbers Generator without Repeat Numbers using VBA in Microsoft Excel 2013

VBA Distinct Random Number Generator

If you want to generate random number in Excel & all the numbers should be unique (no repeat), you can use Excel Rand function to generate random numbers.

In this article, we will learn how to quickly create list of unique random numbers

We need to follow the below steps:

• Click on Developer tab
• From Code group, select Visual Basic

• Enter the following code in the worksheet module

Function DistinctRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant

Dim RandColl As Collection, i As Long, varTemp() As Long

DistinctRandomNumbers = 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)

On Error GoTo 0

Loop Until RandColl.Count = NumCount

ReDimvarTemp(1 To NumCount)

For i = 1 ToNumCount

varTemp(i) = RandColl(i)

Next i

Set RandColl = Nothing

DistinctRandomNumbers = varTemp

Erase varTemp

End Function

Sub Test()

Dim varrRandomNumberList As Variant

varrRandomNumberList = DistinctRandomNumbers(50, 1, 100)

Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _

Application.Transpose(varrRandomNumberList)

End Sub

• After copying the code, you need to type the formula in any cell
• =DistinctRandomNumbers(50, 1, 100)

In this way, we can make a list of distinct random numbers without repetition in Excel 2013.