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

 

img1

 

  • 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)

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

Application.Transpose(varrRandomNumberList)

End Sub

 

img2

 

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

 

img3

 

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

xlsx-1567

Download – Random Number Generator without Repeat Numbers-xlsm

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube