Excel VBA: Random Number Generator with No Duplicates (4 Examples)

This article illustrates 4 examples of random number generator with no duplicates using VBA in Excel. Here we’ll use Excel’s built-in Rnd function to configure our code. Let’s dive into the examples to learn the techniques to generate unique random numbers.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

4 Examples of Random Number Generator with No Duplicates in Excel VBA

Write Code in Visual Basic Editor

To generate random numbers with no duplicates, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Use of VBA Rnd Function to Generate Random Number with No Duplicates

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

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

Code: Insert 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 screenshot shows 10 unique random numbers in the range of 0 and 1.

Code Explanation:
In this code, we used the Rnd function to insert random numbers in the cell range A1:A10. Before inserting a new number, we used a Do While Loop to look for the number in the predefined cell range (A1:A10) whether it already exists or not. To check the existence of the number in the cell range each time, we configured the code with the COUNTIF function, This function checks a new random number in the list of already existing numbers before inserting it.

Read More: Excel Formula to Generate Random Number (5 examples)

2. Random Number Generator for Defined Lowerbound and Upperbound with No Duplicates

To generate random numbers within a defined range, we need to set the lowerbound and upperbound in our VBA code. For information, the lowerbound is the lowest number and the upperbound is the highest number in the range for the random number generator. We can use the following formula in our code.

(upperbound – lowerbound + 1) * Rnd + lowerbound

2.1 Random Number Generator- Decimal

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

Code: Insert 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 screenshot shows 10 unique random numbers in the range of 1 and 10.

2.2 Random Number Generator- Integer

In this illustration, we’ll use the VBA Int function to remove the fractional part from the random numbers.

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

Code: Insert the following code in the visual basic editor and press F5 to run it.

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

Output: The above screenshot shows 20 unique random integer numbers in the range of 1 and 20.

Read More: Random Number Generator in Excel with No Repeats (9 Methods)

Similar Readings

• Random Number Generator with Data Analysis Tool and Functions in Excel
• How to Generate Random Data in Excel (9 Easy Methods)
• Random 5 Digit Number Generator in Excel (7 Examples)
• Random 4 Digit Number Generator in Excel (8 Examples)
• Generate Random Number from List in Excel (4 Ways)

3. Specify Decimal Places for Unique Random Number Generator in Excel VBA

We can use the Round function in our code to specify the number of decimal places in the randomly generated unique numbers. The syntax of the function is-

Round(expression, [numdecimalplaces])

We need to specify the 2nd argument as per our requirement.

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

Code: Insert the following code in the visual basic editor and press F5 to run it.

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 screenshot shows 20 unique random integer numbers with 2 decimal places in the range of 1 and 20.

Read More: Generate Random Number in Excel with Decimals (3 Methods)

4. Develop a Userform for Random Number Generator with No Duplicates in Excel VBA

In this illustration, we’ll show how to use a UserForm in Excel VBA to generate random numbers with no duplicates.

Task: Generate 20 random numbers in cell range A1:B10 using a UserForm with the input values (i) lowerbound (ii) upperbound (iii) number of decimal places.

Create a UserForm:
Follow the steps below to create a UserForm with our desired input fields.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the UserForm option.

• In the UserForm add a label.
• Caption the label as LowerBound in the properties.

• Add two more labels named Upperbund and DecimalPlaces.

• Now add three TextBoxes in the UserForm.

• At this stage, add a CommandButton and name it Generate.

• Now, double click the CommandButton and put 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 the UserForm has appeared.
• Put the lowerbound, upperbound, and the number of decimal places in the UserForm and hit the Generate Button.

Output: In cells A1:B10, there are 20 random numbers with 2 decimal places in the range of 1 to 30.

Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)

Things to Remember

• We could also use the Fix function instead of the Int function to generate unique integer numbers. The function removes the fractional part of a number just like the Int function.

Conclusion

Now, we know how to generate unique random numbers using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Generate Random 10 Digit Number in Excel (6 Methods)
• How to Generate Random Number in a Range with Excel VBA
• Random Number Generator with Normal Distribution in Excel (4 Methods)
• Generate Random Number with Mean and Standard Deviation in Excel
• Auto Generate Invoice Number in Excel (with 4 Quick Steps)
• Generate Random Number Between 0 and 1 in Excel (2 Methods)