This article illustrates 4 examples of random number generators 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.

**Excel VBA for Random Number Generator with No Duplicates: 4 Examples**

**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 >> Visual Basic**.

- Follow
**Insert**>>**Module**.

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

**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 lower-bound and upper-bound in our **VBA** code.

For information, the lower-bound is the lowest number and the upper-bound 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**:

```
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
```

Insert this code and press F5 to run it.

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

**Read More:** How to Generate Random Number with Excel VBA

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

We can generate random numbers with specified decimals using the **VBA Round function**. 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**:

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

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

In this illustration, weâ€™ll show how to create 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) lower-bound (ii) upper-bound (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 3
**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 Number in a Range with Excel VBA

**Things to Remember**

- We could also use the
**VBA 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.

**Download Practice Workbook**

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

**Conclusion**

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

Hi ,

I would love to randomize assigned stations to a user based by theirs “station” knowledge. For exempel :

User Stations

Elvis : 1,5,7,9

Andreas: 1,3,5,8,9

Evelyn : 4,6,7,8,9 and so on .

I am using “GenerateRandomNumNoDuplicates()” from 1-9 , but the users f.ex : Elvis can get randomized number 6 which is not in his knowledge and can not perform on that station and so on.

Please help me out if there is possibility for that .

hello, EMIN! Actually, I haven’t understood what you asked in this comment. Can you please mail us the problem with a little more explanation at this address: [email protected].

We will try our best to solve your problem. thank You! And, keep browsing Exceldemy.

It all works but when I come out of the dev mode and open the excel on its own I cant seem to get this form up without going back into dev mode, then VBA and then F5 again?

Could you please help

Hello

James Ward,To run the code from Excel you can follow these steps:

Developertab >>Macros>> Select anySub Procedurefrom the listRegards

ExcelDemy