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

Set Range with Variable Row Number with VBA in Excel

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.

Data Set to Use VBA Range with Variable Row Number in Excel

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

VBA Code to Use VBA Range with Variable Row Number in Excel

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.

Entering Inputs to Use VBA Range with Variable Row Number in Excel

Again click OK. And you’ll get the names of the first 5 employees selected in your data set.

Output to Use VBA Range with Variable Row Number in Excel


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.

Entering Inputs to Use VBA Range with Variable Row Number in Excel

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

Here it’s 5.

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

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.

Entering Inputs to Use VBA Range with Variable Row Number in Excel

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

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

Click OK. And you’ll get a series of 1 to 5 in the range A4:A8.


Similar Readings:


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

VBA Code to Use VBA Range with Variable Row Number in Excel

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.

Entering Inputs to Use VBA Range with Variable Row Number in Excel

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.

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

Finally, the 4th box will ask you to enter the number to add. Here I’ve entered 10000.

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

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


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

VBA Code to Use VBA Range with Variable Row Number in Excel

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.

Entering Inputs to Use VBA Range with Variable Row Number in Excel

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

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

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

Inserting Inputs to Use VBA Range with Variable Row Number in Excel

Click OK. And you’ll get the whole background of the 1st 5 names highlighted in yellow.


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 Readings

Tags:

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo