In this article, Iâ€™ll show you how you can use the VBA range with a variable row number in Excel. Youâ€™ll learn to work with the VBA range to select a range of cells, insert numbers into a range of cells, perform mathematical operations in a range of cells, and color a range of cells, all with a variable row number.

VBA Code to Set a Range with Variable Row Number in Excel (Quick View)

```
Sub Range_with_Variable_Row_Number()
First_Cell = InputBox("Enter the First Cell of the Range: ")
Row_Number = Str(Range(First_Cell).Row)
Number_of_Rows = InputBox("Enter the Total Number of Rows of the Range: ")
Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))
End Sub
```

**â§**** Explanation of the Code:**

- The code takes two inputs, the first cell of the range, called
**First_Cell**, and the total number of variable rows of the range, called**Number_of_Rows**. - Then it creates a range of rows called
**Rng**, starting from the**First_Cell**up to the total number of rows you want. - For example, if your
**First_Cell**is**B4**and**Number_of_Rows**is**10**, then the output**Rng**is**B4:B13**. - Always use the relative cell reference of the
**First_Cell**, not the absolute or the mixed cell reference (Use**B4**, not**$B$4**or**$B4**).

**Table of Contents**Expand

**How to Use VBA Range with Variable Row Number in Excel: 4 Examples**

Here weâ€™ve got a data set with the *Names *and *Salaries *of some employees of a company called Jupyter Group.

Today weâ€™ll try to work with a range with a variable row number using VBA on this data set.

**1. Select a Range with Variable Row Number with VBA in Excel**

First of all, letâ€™s develop a Macro to select a range with a variable row number using VBA.

Letâ€™s select the names of the 1st **5 **employees.

You can use the following VBA code for this purpose:

**â§**** VBA Code:**

```
Sub Select_Range()
First_Cell = InputBox("Enter the First Cell to Select: ")
Row_Number = Str(Range(First_Cell).Row)
Number_of_Rows = InputBox("Enter the Number of Rows to Select: ")
Rng = First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10)
Range(Rng).Select
End Sub
```

**â§**** Output:**

Run the Macro (**Select_Range**). Youâ€™ll get two input boxes.

The first box will ask you to enter the first cell of the range you want to select. Here itâ€™s **B4**, the cell with the first employee.

Click **OK**. The second Input box will ask you the number of rows you want to select. Here itâ€™s **5**.

Again click** OK**. And youâ€™ll get the names of the first **5** employees selected in your data set.

**2. Insert Numbers into a Range with Variable Row Number in Excel**

You can also insert numbers into a range with a variable row number with VBA in Excel.

You can insert both a fixed number and a series of numbers.

Letâ€™s number the 1st **5 **employees from **1 **to **5**.

That is, insert a series of **1 **to **5** left to the names of the 1st **5** employees (**A4:A8**).

You can use the following VBA code for this purpose:

**â§**** VBA Code:**

```
Sub Insert_Numbers()
First_Cell = InputBox("Enter the First Cell to Insert Number: ")
Row_Number = Str(Range(First_Cell).Row)
Number_of_Rows = InputBox("Enter the Total Number of Rows to Insert Numbers: ")
Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))
Series_or_Fixed = Int(InputBox("Enter 1 to Enter a Series of Numbers: " + vbNewLine + vbNewLine + "OR" + vbNewLine + vbNewLine + "Enter 2 to Enter a Fixed Number: "))
If Series_or_Fixed = 1 Then
Â Â Â Â First_Number = Int(InputBox("Enter the First Number: "))
Â Â Â Â Increment = Int(InputBox("Enter the Increment: "))
Â Â Â Â For i = 1 To Rng.Rows.Count
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = First_Number + (i - 1) * Increment
Â Â Â Â Next i
ElseIf Series_or_Fixed = 2 Then
Â Â Â Â Number = Int(InputBox("Enter the Fixed Number: "))
Â Â Â Â For i = 1 To Rng.Rows.Count
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = Number
Â Â Â Â Next i
End If
End Sub
```

**â§**** Output:**

Run this Macro (**Insert_Numbers**). Youâ€™ll get a few Input boxes.

The 1st box will ask you to enter the first cell to insert the numbers. Here itâ€™s **A4**.

Click **OK**. The 2nd box will ask you to enter the total number of rows to insert numbers.

Here itâ€™s **5**.

The 3rd box will ask you whether you want a series of numbers or a fixed number.

Enter **1** for a series of numbers.

Enter **2** for a fixed number.

Here I want a series of numbers. So Iâ€™ve entered **1**.

The 4th box will ask you the first number of the series. I want a series of **1 to 5**. So, Iâ€™ve entered **1**.

The final box will ask you to enter the increment. In a series of **1, 2, 3, 4, 5,** itâ€™s **1**.

Click **OK**. And youâ€™ll get a series of** 1 **to **5** in the range **A4:A8**.

**3. Perform Mathematical Operation on a Range with Variable Row Number in Excel**

Next, weâ€™ll develop a Macro to perform a mathematical operation on a range with a variable row number.

For example, letâ€™s think for a moment that the chief of Jupyter Group wants to increase the salaries of the first 5 employees by **$10,000**.

Weâ€™ll develop a Macro to accomplish his purpose.

You can use the following VBA code:

**â§**** VBA Code:**

```
Sub Mathematical_Operation()
First_Cell = InputBox("Enter the First Cell to Perform Operation: ")
Row_Number = Str(Range(First_Cell).Row)
Number_of_Rows = InputBox("Enter the Total Number of Rows to Perform Operation: ")
Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))
Operation = Int(InputBox("Enter the Operation to Perform: " + vbNewLine + "Enter 1 for Addition: " + vbNewLine + "Enter 2 for Subtraction: " + vbNewLine + "Enter 3 for Multiplication: " + vbNewLine + "Enter 4 for Division: "))
Operations = Array("Add", "Subtract", "Multiply", "Divide")
Number = Int(InputBox("Enter the Number to " + Operations(Operation - 1) + ": "))
For i = 1 To Rng.Rows.Count
Â Â Â Â If Operation = 1 Then
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = Rng.Cells(i, 1).Value + Number
Â Â Â Â End If
Â Â Â Â If Operation = 2 Then
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = Rng.Cells(i, 1).Value - Number
Â Â Â Â End If
Â Â Â Â If Operation = 3 Then
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = Rng.Cells(i, 1).Value * Number
Â Â Â Â End If
Â Â Â Â If Operation = 4 Then
Â Â Â Â Â Â Â Â Rng.Cells(i, 1) = Rng.Cells(i, 1).Value / Number
Â Â Â Â End If
Next i
End Sub
```

**â§**** Output:**

Run the Macro (**Mathematical_Operation**). Youâ€™ll get **4** Input boxes.

The 1st box, as usual, will ask you to enter the first cell to perform the operation.

In this example, itâ€™s the salary of the 1st employee, cell **C4**.

The 2nd box will ask you the total number of rows to perform the operation.

The 3rd box will ask you to enter the operation you want to perform.

Enter **1** for addition.

Enter **2** for subtraction.

**3** for Multiplication.

And **4** for a Division.

Here I want an addition. So I have entered **1**.

Finally, the 4th box will ask you to enter the number to add. Here Iâ€™ve entered **10000**.

Click **OK**. And you will find the salaries of the first 5 employees increased by **$10,000**.

**Read More: **Excel VBA: Set Range by Row and Column Number

**4. Color Cells of a Range with Variable Row Number in Excel**

Finally, weâ€™ll develop a Macro to color the cells of a range with a variable row number in Excel.

Letâ€™s color the names of the 1st five employees red.

You can use the following VBA code:

**â§**** VBA Code:**

```
Sub Color_Range()
First_Cell = InputBox("Enter the First Cell to Color: ")
Row_Number = Str(Range(First_Cell).Row)
Number_of_Rows = InputBox("Enter the Total Number of Rows to Color: ")
Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))
Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))
Background_or_Text = Int(InputBox("Enter 1 to Color the Whole Background of the Cells: " + vbNewLine + vbNewLine + "Or" + vbNewLine + vbNewLine + "Enter 2 to Color Only the Texts: "))
For i = 1 To Rng.Rows.Count
Â Â Â Â If Background_or_Text = 1 Then
Â Â Â Â Â Â Â Â Rng(i, 1).Interior.ColorIndex = Color_Code
Â Â Â Â ElseIf Background_or_Text = 2 Then
Â Â Â Â Â Â Â Â Rng.Cells(i, 1).Characters(1, Len(Rng.Cells(i, 1))).Font.ColorIndex = Color_Code
Â Â Â Â End If
Next i
End Sub
```

**â§**** Output:**

Run the Macro (**Color_Range**). Youâ€™ll get 4 input boxes.

The 1st box will ask you to enter the first cell to color.

Here itâ€™s the first employee, cell **B4**.

The 2nd box will ask for the total number of rows to color. Here itâ€™s **5**.

The **3rd** box will ask you to choose the color. Enter any color index according to the **Microsoft Color Code**.

Here Iâ€™ve entered **6** (For **yellow** color).

The final box will ask whether you want to color the whole background of the cells or the only text.

Iâ€™ve entered **1** (Whole background).

Click **OK**. And youâ€™ll get the whole background of the 1st 5 names highlighted in yellow.

**Read More:** How to Use Range with Variable Row and Column with Excel VBA

**Download Practice Workbook**

**Conclusion**

Using these methods, you can use the VBA range with a variable row number in Excel. Do you have any questions? Feel free to ask us.