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

Get FREE Advanced Excel Exercises with Solutions!

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


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

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


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

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


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.


Related Articles


<< Go Back to Rows and Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo