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

**Download Practice Workbook**

**4 Examples to Use VBA Range with Variable Row Number in Excel (Quick View)**

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 **1st** box will ask you to enter the 1st cell of the range you want to select. Here it’s **B4**, the cell with the 1st employee.

Click **OK**. The 2nd 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.

**Read More:** **How to Get Row Number from Range with Excel VBA (9 Examples)**

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

**Read More:** **How to Use Range with Variable Row Number in Excel**

**Similar Readings**

**VBA to Use Range Based on Column Number in Excel (4 Methods)****How to Select Range Based on Cell Value VBA (7 Ways)****Use the Range Object of VBA in Excel (5 Properties)****VBA to Set Range in Excel (7 Examples)****How to Find Row Number Using VBA in Excel (4 Macros)**

**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:**** Find String in Column and Return Row Number in Excel (7 Ways)**

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

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 Get Row Number of Current Cell in Excel (4 Quick Ways)**

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

## Related Articles

**How to Use VBA Range Offset (11 Ways)****VBA for Each Cell in Range in Excel (3 Methods)****Loop through a Range for Each Cell with Excel VBA (Ultimate Guide)****Excel VBA Copy Range to Another Sheet (8 Easiest ways)****Excel VBA: Find String in Column and Return Row Number****How to Increment Row Number in Excel Formula (6 Handy Ways)**