Not only in Excel **VBA** but also while working with almost all the programming languages, we often need to generate one or more random number (s) in between a range. In this article, I’ll show you how you can generate random numbers in a range with examples including both repetition and not-repetition.

Generate a Random Number in a Range with Excel VBA (Quick View)

```
Sub Random_Numbers_with_Rnd()
Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)
End Sub
```

**Table of Contents**hide

**Download Practice Workbook**

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

**2 Methods to Generate Random Number in a Range with Excel VBA**

There are **2** methods available to generate a random number in a range with Excel **VBA**.

**1. Generate Random Number in a Range with the Excel RandBetween Function in VBA**

First of all, we’ll generate random numbers within a range using the **RandBetween function** of Excel in **VBA**.

The syntax of the **RandBetween **function is very simple.

`=RANDBETWEEN (bottom, top)`

It takes two number arguments called bottom and top and generates a random number between them, including them.

Therefore, to generate a random number between **1** and **100**, the **VBA** code will be:

**⧭ VBA Code:**

```
Sub Random_Numbers_with_RandBetween()
Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)
End Sub
```

**⧭ Output:**

Run the code after inserting an extra line **MsgBox Random_Number**.

It’ll display a random number between **11** and **20**.

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

**Similar Readings**

**Generate Random Number with Mean and Standard Deviation in Excel****Random Number Generator with Normal Distribution in Excel (4 Methods)****Generate Random Number between Two Numbers in Excel (4 Ways)****Generate Random Number in Excel with Decimals (3 Methods)****How to Generate Random 10 Digit Number in Excel (6 Methods)**

**2. Generate Random Number in a Range with VBA Rnd Function**

Now, we’ll generate random numbers within a range using the **VBA** **Rnd function.**

The syntax of the **Rnd **function is:

`=Rnd ()`

It generates a random number between **0** and **1**.

To generate a random number between **1** and **100**, the **VBA** code will be:

**⧭ VBA Code:**

```
Sub Random_Numbers_with_Rnd()
Bottom = 11
Top = 20
Random_Number = Bottom + Int(Rnd() * (Top - Bottom + 1))
End Sub
```

**⧭ Output:**

Again run the code after inserting an extra line **MsgBox Random_Number**.

It’ll display a random number between **11** and **20**.

**Read More:** **How to Generate Random Number with Excel VBA (4 Examples)**

**Generating Random Numbers in a Range with Repetition**

Here we’ve got a data set with the names of **10 **students in a school.

There are **10** teams in the school consisting of numbers between **1** to **10**. We need to assign each student to any of the **10** teams available randomly.

Therefore, we need to generate a series of random numbers between 1 to 10 in the range **C4:C13 **of the worksheet.

There may be more than one student in one group. So there may be repetitions.

We can accomplish this using both the **RandBetween function** and the **Rnd function**. But we’ll use the **RandBetween** function here.

If you wish, you can try the **Rnd** function on your own.

The **VBA **code with the **RandBetween** function will be:

**⧭ VBA Code:**

```
Sub Random_Numbers_with_Repetition()
Set Rng = Range("C4:C13")
Bottom = 1
Top = 10
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Rng.Cells(i, j) = Application.WorksheetFunction.RandBetween(Bottom, Top)
Next j
Next i
End Sub
```

**⧭ Output: **

Run this code. It’ll create a series of random numbers from **1** to **10** in the range **C4:C13** of the worksheet with repetition.

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

**Generating Random Numbers in a Range without Repetition**

Now, we’ll assign each student a unique team. That means, we’ll generate a series of random numbers between **1** to **10 **in the range **C4:C13**, this time without repetition.

We’ll use the **Rnd function** of **VBA** here.

**⧭ VBA Code:**

```
Sub Random_Numbers_without_Repetition()
Set Rng = Range("C4:C13")
Bottom = 1
Top = 10
Dim Numebrs() As Variant
ReDim Numbers(Top - Bottom)
For i = LBound(Numbers) To UBound(Numbers)
Numbers(i) = Bottom + i
Next i
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Index = LBound(Numbers) + Int(Rnd() * (UBound(Numbers) - LBound(Numbers) + 1))
Rng.Cells(i, j) = Numbers(Index)
For k = Index To UBound(Numbers) - 1
Numbers(k) = Numbers(k + 1)
Next k
If UBound(Numbers) <> 0 Then
ReDim Preserve Numbers(UBound(Numbers) - 1)
End If
Next j
Next i
End Sub
```

**⧭ Output: **

Run this code. It’ll create a series of random numbers from **1** to **10** in the range **C4:C13** of the worksheet without repetition.

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

**Things to Remember**

The **Rnd function** is a function of **VBA**. That’s why we used it directly in our codes. On the other hand, the **RandBetween function** is not a **VBA** function, it’s actually an Excel function. That’s why we had to use it along with the **Application.WorksheetFunction** property.

**Conclusion**

So, these are the ways to generate random numbers in a range using **VBA** in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site **ExcelDemy** for more posts and updates.

**Related Articles**

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