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



        On Error Resume Next

i = CLng(Rnd * (ULimit - LLimit) + LLimit)

RandColl.Addi, CStr(i)

        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 = _


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.


Download - Random Number Generator without Repeat Numbers-xlsm

Users are saying about us...

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube