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)

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

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

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

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

Similar Readings

• How to Switch Rows and Columns in Excel Chart (2 Methods)
• Transpose Multiple Columns to Rows in Excel
• How to Reference Cell by Row and Column Number in Excel (4 Methods)
• [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)
• How to Convert Column Letter to Number Chart in Excel (4 Ways)

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

• How to Add Multiple Rows and Columns in Excel (Every Possible Way)
• Hide Rows and Columns in Excel: Shortcut & Other Techniques
• Column Letter to Number Converter in Excel (5 Easy Examples)
• [Fixed!] Excel Columns Are Labeled with Numbers Not Letters
• How to Return Column Number of Match in Excel (5 Useful Ways)
• VBA to Convert Column Number to Letter in Excel (3 Methods)
• How to Use VBA Range Based on Column Number in Excel