How to Generate Random Numbers in a Range with Excel VBA

Here’s a quick overview of the code that assigns random numbers.

Sub Random_Numbers_with_Rnd()

Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)

End Sub

Method 1 – Using the Excel RandBetween Function in VBA

We’ll generate random numbers within a range using the RandBetween function of Excel in VBA.

The syntax of the RandBetween function is:

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

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

Sub Random_Numbers_with_RandBetween()

Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)

End Sub

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

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

Method 2 – Using the VBA Rnd Function

We’ll generate random numbers within a range using the VBA Rnd function.

The syntax of the Rnd function is:

It generates a random number between 0 and 1.

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

Sub Random_Numbers_with_Rnd()

Bottom = 11
Top = 20
Random_Number = Bottom + Int(Rnd() * (Top - Bottom + 1))

End Sub

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

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

Generating Random Numbers in a Range with Repetition

Below is a dataset with the names of 10 students in a school.

There are 10 teams in the school, each with a number between 1 and 10. We need to assign each student to one of the 10 teams available randomly.

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.

You can try the Rnd function on your own.

• The VBA code with the RandBetween function will be:

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

• 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 Number with Excel VBA

Generating Random Numbers in a Range without Repetition

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:

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

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

Things to Remember

The Rnd function is a VBA function, so 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, so we had to use it along with the Application.WorksheetFunction property.

Download the Practice Workbook

Related Articles

• Excel VBA: Random Number Generator with No Duplicates
• Random Number Generator with Normal Distribution in Excel