In this article, I’ll show you how you can set a range by using the row number and the column number in VBA in Excel.
Excel VBA: Set Range by Row and Column Number (Quick View)
How to Set Range by Row and Column Number in Excel VBA: 3 Suitable Examples
So, without further delay, let’s go to our main discussion today. Here, we’ll discuss 3 methods to set a range by the row and column number but with Excel VBA.
1. Set Range by Row and Column Number Using the Cell Address in VBA
You can set a range by the row number and column number using the cell address directly.
For example, if you want to set the range B4:D13, you can use Range(“B4:D13”).
To set the range using the VBA Range function with the name Rng (Obviously you can use any name according to your wish), you can use the following line of code:
Set Rng = Range("B4:D13")
⧭ Output:
To verify that it actually contains the range B4:D13, you can insert this piece of line to select the range.
Rng.Select
Now run the code. You’ll find the range B4:D13 in the active worksheet selected.
⧭ Notes:
If you don’t specify the worksheet name before the range, it sets the range from the active worksheet.
But in case you want to set a range from a worksheet that’s not active, you have to specify the name of the worksheet before the range.
For example, to set the range B4:D13 from Sheet1, you can use:
Set Rng = Worksheets("Sheet1").Range("B4:D13")
You can mention the name of the workbook in the same way.
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("B4:D13")
2. Set Range by Row and Column Number Using the Cells Property of VBA in Excel
Next, you can set a range by the row number and column number using the Cells Property of VBA.
For example, if you want to set the range B4:D13 (From Row 4, Column 2 to Row 13, Column 4), you can use Range(Cells(4, 2), Cells(13, 4)).
Again to set the range with the name Rng (Obviously you can use any name according to your wish), you can use the following line of code:
Set Rng = Range(Cells(4, 2), Cells(13, 4))
⧭ Output:
Again to verify that it actually contains the range B4:D13, insert this piece of line.
Rng.Select
And then run the code. You’ll get the range B4:D13 in the active worksheet selected.
⧭ Notes:
Obviously, like the earlier method, you can insert the names of the worksheet and the workbook before in case they are not active.
Read More: VBA Range with Variable Row Number in Excel
3. Set Range within Another Range Using the Cells Function of VBA in Excel
Finally, you can set a range within another range using the Cells Property of VBA.
For example, if you want to set the range from Row 2, Column 2 to Row 3, Column 3 within the range B4:D13, you can use Range(“B4:D13”).Range(Cells(2, 2), Cells(3, 3)).
Again to set the range with the name Rng, use the following line of code:
Set Rng = Range("B4:D13").Range(Cells(2, 2), Cells(3, 3))
⧭ Output:
Again to verify that it actually contains the desired range, insert:
Rng.Select
And then run the code. It’ll select the range from Row 2, Column 2 to Row 3, Column 3 within the range B4:D13.
⧭ Notes:
Like the earlier methods, you can insert the names of the worksheet and the workbook before.
Read More: VBA to Set Range in Excel
Things to Remember
You’ve learned to set a range using the row number and the column number with VBA. But there’s a built-in property in Excel called the UsedRange property that covers the whole range in a worksheet that has been covered. You can use:
Set Rng=ActiveSheet.UsedRangeIt sets the used range in the active worksheet.
Or
Set Rng=Worksheets("Sheet1").UsedRangeIt sets the used range in the worksheet called Sheet1.
You may be interested to know it in detail.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
So, these are the methods to set a range by the row and column number using VBA in Excel. Do you have any questions? Feel free to ask us.
