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.

Sample Data

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.

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

Step 1: Create a VBA Module

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

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

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

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

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.

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

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

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

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

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

Step 5: Type a Row Number

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

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

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

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

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

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

Step 2: Get the Selection

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

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

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

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

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.

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

Read More: Excel VBA: Copy Dynamic Range to Another Workbook


Similar Readings


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

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

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.

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


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

Sample Data

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.

Sample Data

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

Sample Data

Step 2: Insert the Row Number

  • Type any row number.

Sample Data

Step 3: Insert the Column Number

  • Type any column number.

Sample Data

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.

Sample Data


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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo