How to Use Range with Variable Row and Column with Excel VBA

It is time-consuming when you want to format cells by selection and work with large data at the same time. But with the help of the VBA, we can program to do it quite easily. In this tutorial, we will show you how to use variable rows and variable columns in a range with Excel VBA.

How to Use Range with Variable Row and Column with Excel VBA: 5 Ways

In the following section, we will demonstrate 5 ways to create variable rows and columns. In Addition, we will show you how to use them to format or apply any customization to the variable range. A sample data set is represented in the image below to utilize the task.

1. Format a Specific Range by Applying Variable Row with Excel VBA

Let’s say, we want to select a variable range by applying for a variable row number. We will select the range B5:C10 and apply the Font Color (Maroon) in the range. To do so, follow the simple steps below.

♠ Step 1: Create a VBA Module

• First of all, press  Alt +  F11  to start the VBA Macro.
• Click on the Insert.
• Then, select Module.

♠ Step 2: Write a VBA Code

• Write the following VBA codes.

Sub Variable_row_Select() 
'declare a variable for row number
Dim Row_Number As Integer 
'Enter a Input box, type 10 for the row number
Row_Number = InputBox("Type the row number") 
'Insert the variable 'row_num' to select the first 5 rows containing data
Sheets("Sheet1").Range(Cells(5, 2), Cells(Row_Number, 3)).Select 
End Sub

♠ Step 3: Run the Program

• Firstly, Save the program and press  F5  to run.
• Therefore, the Input box will appear, and type 10 as the row number.

• Finally, press Enter to see that the range with (Row 5, Column 2) to (Variable Row 10, Column 3) will be selected.

♠ Step 4: Apply Font Color to the Range

• To add font color in the selected range, paste the following VBA codes.

Sub Variable_row_Font()
'declare a variable for row number
Dim Row_Number As Integer 
'Enter a Input box, type 10 for the row number
Row_Number = InputBox("Type the row number") 
'Insert the variable 'row_num' to select the first 6 rows of containing data
Sheets("Sheet1").Range(Cells(5, 2), Cells(Row_Number, 3)).Select
'Input a color to the font in Maroon
With Selection
        .Font.Color = RGB(128, 0, 0)
End With 
End Sub

♠ Step 5: Type a Row Number

• Type a row number (10) in the Input box.
• Click OK.

• Therefore, the selected range will be colored as shown in the image below.

2.  Customize a Dynamic Range by Applying Variable Row with Excel VBA

When your used range is so large that you cannot differentiate the row number, you may need to use the last used row as your variable row. To do it, follow the outlined steps below.

♠ Step 1: Enter a VBA Code

• Select a new Module from the Insert
• In the new Module, write the following VBA program.

Sub Variable_Dynamic_Row() 
'declare a variable for row number
Dim Last_Used_Row As Integer 
'Define variable to the used range
Last_Used_Row = Worksheets("Sheet2").UsedRange.Rows.Count 
'Apply the variable to select current last row in the range
Sheets("Sheet2").Range(Cells(5, 2), Cells(Last_Used_Row, 5)).Select 
End Sub

♠ Step 2: Get the Selection

• After running the program, your range will be selected up to your last used row.

♠ Step 3: Apply a Font Color

• To mark the selected range or to edit, paste the following VBA codes.

Sub Variable_Dynamic_Row() 
'declare a variable for row number
Dim Last_Used_Row As Integer 
'Define variable to the used range
Last_Used_Row = Worksheets("Sheet2").UsedRange.Rows.Count 
'Apply the variable to select current last row in the range
Sheets("Sheet2").Range(Cells(5, 2), Cells(Last_Used_Row, 5)).Select 
With Selection
        .Font.Color = RGB(128, 0, 0)
End With  
End Sub

♠ Step 4: Final Result

• Finally, save the program and run it by pressing  F5 .
• As a result, the range with your last used row is formatted with color.

Read More: VBA Range with Variable Row Number in Excel

3. Format a Specific Range by Applying Variable Column with Excel VBA

Similar to variable rows, you can apply variable columns with Excel VBA. B5 (Row 5, Column 2) cell is the first cell in the range, and Row 8 is the last row in the range; the last column is the variable column. Follow the procedures below to apply the variable column.

♠ Step 1: Write a VBA code

• In a new Module, write the following VBA code.

Sub Variable_Column_Font() 
'declare a variable for row number
Dim Column_num As Integer 
'Enter a Input box, type 5 for the Column number
Column_num = InputBox("Type the Column number") 
'Insert the variable 'Column_num' to select the first 5 Columns of containing data
Sheets("Sheet3").Range(Cells(5, 2), Cells(8, Column_num)).Select 
'Input a color to the font in Maroon
With Selection
        .Font.Color = RGB(128, 0, 0)
End With 
End Sub

♠ Step 2: Run the Program

• To run the program, press  F5  after saving.
• Therefore, you will see the following results as your selected cells are in the range B5:E8.

4.  Customize a Dynamic Range by Applying Variable Column with Excel VBA

In addition to the previous one, you can customize the variable column to perform dynamically. As you increase data in a column that will add to the selection. Follow the simple instructions below to do so.

♠ Step 1: Paste a VBA Code

• Paste the following VBA codes into a new Module.

Sub Variable_Dynamic_Column() 
'declare a variable for column number
Dim lastColumn As Integer
'Define variable to the used range
lastColumn = Worksheets("Sheet4").UsedRange.Columns.Count 
'Apply the variable to select cuurent last column in the range
Sheets("Sheet4").Range(Cells(5, 2), Cells(8, lastColumn)).Select
'colour the font of selected cells in Maroon
With Selection
        .Font.Color = RGB(128, 0, 0)
End With 
End Sub

♠ Step 2: Run the program

• Firstly, Save the program and press  F5  to run.
• As a result, the range is selected and formatted up to the last used column in the worksheet.

5. Create a Range with Both Variable Row and Variable Column with Excel VBA

Importantly, you can use both variable rows and variable columns by a selection of your choice. The first cell in our range selection is B5 (Row 5, Column 2) and the last range will vary upon our selection. Follow the outlined steps below to accomplish the task.

♠ Step 1: Type a VBA Code

• Firstly, create a new Module.
• Then, write the following VBA codes.

Sub Variable_Column_Row() 
'declare a variable for row number
Dim Row_Number As Integer
'declare a variable for column number
Dim Column_num As Integer 
'Define variables - type 8 for row number and 4 for column
Row_Number = InputBox("Type the row number")
Column_num = InputBox("Type the Column number") 
'Apply the variable to select rows and  columns in the range
Sheets("Sheet5").Range(Cells(5, 2), Cells(Row_Number, Column_num)).Select 
With Selection
        .Font.Color = RGB(128, 0, 0)
End With 
End Sub

♠ Step 2: Insert the Row Number

• Type any row number.

♠ Step 3: Insert the Column Number

• Type any column number.

♠ Step 4: Get the Final Results

• Consequently, as you select (Row, Column) = (8,5), you will get the final result as shown in the below image.

Read More: Excel VBA: Set Range by Row and Column Number

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Finally, I hope you now understand how to use range with variable rows and columns with Excel VBA. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

Stay with us and continue to learn.

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