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

It is time consumable 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 row and variable column in a range with Excel VBA.

Download Practice Workbook

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

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

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.

Read More: How to Use VBA to Set a Range Variable to Selection in Excel (5 Methods)

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

When your used range is that much large you cannot differentiate the row number, you may need to use the last used row as your variable row. To have done 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: Excel VBA: Copy Dynamic Range to Another Workbook

Similar Readings

• How to Use VBA for Each Row in a Range in Excel
• VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)
• Excel VBA to Loop through Range until Empty Cell (4 Examples)
• How to Convert Range to Array in Excel VBA (3 Ways)
• How to Use VBA to Select Range from Active Cell in Excel (3 Methods)

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

Read More: Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)

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.

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.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.

Related Articles

• Excel VBA to Select Used Range in Column (8 Examples)
• VBA to Loop Through Rows in Range in Excel (6 Examples)
• Excel VBA: Get Range of Cells with Values (7 Examples)
• How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)
• Excel VBA: Loop Through Columns in Range (5 Examples)