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

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.