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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF