Excel VBA: Set Range by Row and Column Number (3 Examples)

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)

VBA Code to Set Range by Row and Column Number in Excel


Download Practice Workbook

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


3 Suitable Examples to Set Range by Row and Column Number in Excel VBA

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 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 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")

VBA Code to Set Range by Row and Column Number in Excel VBA

⧭ Output:

To verify that it actually contains the range B4:D13, you can insert this piece of line to select the range.

Rng.Select

Select Range to Set Range by Row and Column Number in Excel VBA

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")

VBA Code to Set Range by Row and Column Number in Excel VBA

You can mention the name of the workbook in the same way.

Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("B4:D13")

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)


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))

VBA Code to Set Range by Row and Column Number in Excel

⧭ Output:

Again to verify that it actually contains the range B4:D13, insert this piece of line.

Rng.Select

Selecting Range to Set Range by Row and Column Number in Excel VBA

And then run the code. You’ll get the range B4:D13 in the active worksheet selected.

Output to Set Range by Row and Column Number in Excel VBA

⧭ 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: [Fixed!] Rows and Columns Are Both Numbers in Excel


Similar Readings


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

Selecting Range to Set Range by Row and Column Number in Excel VBA

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: [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)


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

It sets the used range in the active worksheet.

Or

Set Rng=Worksheets("Sheet1").UsedRange

It sets the used range in the worksheet called Sheet1.

You may be interested to know it in detail.


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. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo