This is an overview:

```
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
```

**Code Breakdown**

- The code takes two inputs, the first cell in the range, called
**First_Cell**, and the total number of variable rows in the range, called**Number_of_Rows**. - It creates a range of rows called
**Rng**, starting from the**First_Cell**up to the total number of rows. - If your
**First_Cell**is**B4**and the**Number_of_Rows**is**10**, the output**Rng**is**B4:B13**. - Use the relative cell reference of the
**First_Cell**, not the absolute or the mixed cell reference (Use**B4**, not**$B$4**or**$B4**).

The datasetÂ contains employees’ *Names *and *Salaries.*

**Example 1 – Select a Range with a Variable Row Number with VBAÂ **

- Select the names of the 1st
**5**employees. - Use the following 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,
**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,**5**.

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

**ExampleÂ 2 – Insert Numbers into a Range with a Variable Row Number in Excel**

- Number the 1st
**5**employees from**1**to**5**. - Use the following 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 the Macro (
**Insert_Numbers**). Youâ€™ll see Input boxes. - The 1st box will ask you to enter the first cell to insert the numbers. Here,
**A4**.

- Click
**OK**. The 2nd box will ask you to enter the total number of rows to insert numbers. Here,**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, a series of numbers.

- The 4th box will ask you the first number of the series:Â
**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**A4:A8**.

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

The salaries of the first 5 employees will be increased by **$10,000**.

Use the following 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 will ask you to enter the first cell to perform the operation. Here, the salary of the 1st employee in
**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, an addition.

- The 4th box will ask you to enter the number to add. Here,
**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

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

Color the names of the 1st five employees.

Use the following 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, the first employee,
**B4**.

- The 2nd box will ask for the total number of rows to color:
**5**.

- The
**3rd**box will ask you to choose the color. Here,**6 –**Â**yellow**.

- The final box will ask whether you want to color the whole background of the cells or the only text. Here,
**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**

## Related Articles

- Excel VBA Range Function
- Excel VBA: Get Range of Cells with Values
- VBA to Set Range in Excel
- How to Use VBA to Set a Range Variable to Selection in Excel