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

We will demonstrate 5 ways to create variable rows and columns. We’ll use the following sample dataset with product prices, quantities, and sales.

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

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.

Step 1 – Create a VBA Module

Press Alt + F11 to open the VBA window.
Click on Insert.
Select Module.

Step 2 – Insert VBA Code

Insert the following VBA code in the module.

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 Code

Save the file and press F5 to run.
An Input box will appear. Type 10 as the row number.

Press Enter to see that the range from (Row 5, Column 2) to (Variable Row 10, Column 3) will be selected.

Step 4 – Apply a Font Color to the Range

Paste the following VBA code in a module.

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

Run the code again.
Type a row number (10) in the Input box.
Click OK.

The selected range will be colored as shown in the image below.

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

We’ll get the used row as your variable row.

♠ VBA Code

Use the following VBA code.

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

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

To mark the selected range or to edit, paste the following VBA code and run it.

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

Final Result

The range with your last used row is formatted with color.

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

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

VBA code

In a new Module, insert 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

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

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

VBA Code

Run the following VBA code in 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

The range is selected and formatted up to the last used column in the worksheet.

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

The first cell in our range selection is B5 (Row 5, Column 2) and the last range will vary from an InputBox.

VBA Code

Use the following VBA code.

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