Excel VBA: Random Number Generator with No Duplicates – 4 Examples

Enter a Code in the Visual Basic Editor

To generate random numbers with no duplicates, open Visual Basic Editor and use a VBA code. 

• Go to Developer >> Visual Basic.

• Select Insert >> Module.

• Enter the code in the visual code editor and press F5 to run it.

Example 1 – Use VBA with the Rnd Function to Generate Random Numbers with No Duplicates

The Rnd function is used in Excel VBA to generate random numbers between 0 and 1 exclusive.

Task: Generate 10 random numbers between 0 and 1 in A1:A10.

Code: Enter the following code in the Visual Basic editor and press F5 to run it.

Public Sub GenerateRandomNumNoDuplicates()
    Set cellRange = Range("A1:A10")
    cellRange.Clear
    For Each Rng In cellRange
        randomNumber = Rnd
        Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
            randomNumber = Rnd
        Loop
        Rng.Value = randomNumber
    Next
End Sub

Output: The above image shows 10 unique random numbers between 0 and 1.

Code Explanation:

The Rnd function inserts random numbers in A1:A10.

Before inserting a new number, the Do While Loop looks for the number in A1:A10.

To check the existence of the number in the cell range, the COUNTIF function is used.

This function checks a new random number in the list before inserting it.

Example 2 – Random Number Generator with a Defined Lowerbound and Upperbound and No Duplicates

To generate random numbers within a defined range, set the lower-bound and upper-bound in the VBA code.

The lower-bound is the lowest number and the upper-bound is the highest number in the range.

Use the following formula in the code:

(upperbound – lowerbound + 1) * Rnd + lowerbound

2.1 Random Number Generator- Decimal

Task: Generate 10 random numbers between 10 and 20 in A1:A10.

Code: Enter the following code in the Visual Basic editor and press F5 to run it.

Public Sub GenerateRandomNumNoDuplicates()
  lowerbound = 1
  upperbound = 10
Set cellRange = Range("A1:A10")
cellRange.Clear
For Each Rng In cellRange
    randomNumber = (upperbound - lowerbound + 1) * Rnd + lowerbound
    Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
        randomNumber = (upperbound - lowerbound + 1) * Rnd + lowerbound
    Loop
    Rng.Value = randomNumber
    Next
End Sub

Output: The above image shows 10 unique random numbers between 1 and 10.

2.2 Random Number Generator- Integer

Use the VBA Int function to remove the fractional part of random numbers.

Task: Generate 20 random integer numbers between 1 and 20 in A1:B10.

Code:

Public Sub GenerateRandomNumNoDuplicates()
  lowerbound = 1
  upperbound = 20
Set cellRange = Range("A1:B10")
cellRange.Clear
For Each Rng In cellRange
    randomNumber = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
        randomNumber = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Loop
    Rng.Value = randomNumber
    Next
End Sub

Enter the code and press F5 to run it.

Output: The above image shows 20 unique random integer numbers between 1 and 20.

Read More: How to Generate Random Number with Excel VBA

Example 3 – Specify Decimal Places in the Unique Random Number Generator in Excel VBA

Use the VBA Round function. The syntax of the function is:

Round(expression, [numdecimalplaces])

Specify the 2nd argument.

Task: Generate 20 random numbers with 2 decimal places between 1 and 20 in A1:B10.

Code:

Public Sub GenerateRandomNumNoDuplicates()
  lowerbound = 1
  upperbound = 20
Set cellRange = Range("A1:B10")
cellRange.Clear
For Each Rng In cellRange
    randomNumber = Round((upperbound - lowerbound + 1) * Rnd + lowerbound, 2)
    Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
        randomNumber = Round((upperbound - lowerbound + 1) * Rnd + lowerbound, 2)
    Loop
    Rng.Value = randomNumber
    Next
End Sub

Output: The above image shows 20 unique random integer numbers with 2 decimal places between 1 and 20.

Example 4 – Develop a UserForm for a Random Number Generator with No Duplicates in Excel VBA

Create a UserForm in Excel VBA to generate random numbers with no duplicates.

Task: Generate 20 random numbers in A1:B10 using a UserForm with: the input values (i) lower-bound (ii) upper-bound (iii) number of decimal places.

Create a UserForm:

• Go to the Developer tab.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click Insert and select UserForm.

• In the UserForm, add a label.
• Name it LowerBound in Properties.

• Add two more labels: Upperbund and DecimalPlaces.

• Add 3 TextBoxes in the UserForm.

• Add a CommandButton and name it Generate.

• Double-click the CommandButton and use the following code in the code editor.

Private Sub CommandButton1_Click()
Dim lowerbound As Integer
Dim upperbound As Integer
Dim decimalPlaces As Integer
lowerbound = Val(TextBox1.Text)
upperbound = Val(TextBox2.Text)
decimalPlaces = Val(TextBox3.Text)
Set cellRange = Range("A1:B10")
cellRange.Clear
For Each Rng In cellRange
    randomNumber = Round((upperbound - lowerbound + 1) * Rnd + lowerbound, decimalPlaces)
    Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
        randomNumber = Round((upperbound - lowerbound + 1) * Rnd + lowerbound, decimalPlaces)
    Loop
    Rng.Value = randomNumber
    Next
End Sub

• Press F5 to run the code and display the UserForm.
• Insert the lowerbound, upperbound, and the number of decimal places in the UserForm.
• Click Generate.

Output: In A1:B10, there are 20 random numbers with 2 decimal places between 1 and 30.

Read More: How to Generate Random Number in a Range with Excel VBA

Things to Remember

• You can also use the VBA Fix function instead of the Int function to generate unique integer numbers.

Download Practice Workbook

Download the practice workbook to exercise.

Related Articles

• Random Number Generator with Normal Distribution in Excel