 # 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. ## Users are saying about us...

1. numbers are repeating itself

2. Well this is obviously incorrect - even the example shows 10 being repeated twice.

3. This code generates single random not range of distinct random number. Not useful code.

4. Thanks for the code. Just what I needed. The screendump is naturally not correct, but the code works. Just annoyed that I didn't think of using a collection, which are knew 'all' about.

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.