VBA Range with Variable Row Number in Excel (4 Examples)

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

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.

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