How to Use the UsedRange Property of VBA in Excel (4 Ways)

In this article, I’ll show you how you can use the UsedRange property of VBA in Excel. You’ll learn to use the UsedRange property for a closed range, for a scattered range, for an inactive worksheet, and also for an inactive workbook.


Download Practice Workbook

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


An Introduction to the UsedRange Property of VBA in Excel

The UsedRange property of VBA returns a Range object. It returns a range consisting of all the cells in a worksheet that has been used including an empty row in the beginning.

In a VBA code, the UsedRange property is to be used along with the worksheet name. So the common syntax for using the UsedRange property for the active worksheet is:

Dim Rng As Range

Set Rng = ActiveSheet.UsedRange

VBA Code with the UsedRange Property of VBA

Notes:

  • Here Rng is the name of the Range returned by the UsedRange property. You can use anything you like.
  • To exercise the UsedRange property in a worksheet other than the active one, use the name of the worksheet instead.

For example, to apply it in a worksheet called Sheet1, insert:

Set Rng = Worksheets("Sheet1").UsedRange

4 Ways to Use the UsedRange Property of VBA in Excel

Here are the 4 most common ways to use the UsedRange property in VBA.

1. VBA UsedRange Property for a Closed Range

First of all, we’ll use the VBA UsedRange property for a worksheet with a closed range.

It’ll return the whole range including an empty row in the beginning.

Here we’ve got a worksheet called Sheet1 that contains a closed range consisting of the names, joining dates, and salaries of some employees of a company.

Worksheet to Use the UsedRange Property of VBA

Now if you use the UsedRange property on this worksheet, it’ll return the range B2:C13 (Including an empty row in the beginning).

If Sheet1 is active, you can use:

Dim Rng As Range

Set Rng = ActiveSheet.UsedRange

UsedRange in VBA Code

Or you can use:

Dim Rng As Range

Set Rng = Worksheets("Sheet1").UsedRange

Output:

We’ve used the Select property of a Range within the code. So, if we run the code, it’ll select the range B2:D13 of Sheet1.

Selecting Range with the UsedRange Property of VBA

Read More: How to Use the Range Object of VBA in Excel (5 Properties)


2. VBA UsedRange Property for a Scattered Range

If you have a scattered range in any worksheet, the UsedRange property will return a range including the empty cells in between.

Now, in Sheet1, we have the total salary, highest salary, and lowest salary scattered in various places, from cell B3 to G3,  like this:

Now use any of the two lines of codes to use the UsedRange property.

Dim Rng As Range

Set Rng = ActiveSheet.UsedRange

UsedRange in VBA Code

Or

Dim Rng As Range

Set Rng = Worksheets("Sheet1").UsedRange

Output:

It returns all the cells within the range B2:G3 of Sheet1 including the blank cells (Including an empty row in the beginning). As we’ve used the Select property of the Range, it’ll select the range B2:G3.

Selecting Scattered Range with the UsedRange Property of VBA in Excel

Read More: End of a Range Using VBA in Excel (With Examples)


Similar Readings


3. VBA UsedRange Property for an Inactive Worksheet

If we want to exercise the UsedRange property on an inactive worksheet, you have to mention the name of the worksheet in the beginning.

For example, here my active worksheet is Sheet1.

To use the UsedRange property in Sheet2, we have to use:

Dim Rng As Range

Set Rng = Worksheets("Sheet2").UsedRange

VBA Code with the UsedRange Property of VBA

It’ll select all the used cells in the worksheet called Sheet2.

Read More: VBA to Set Range in Excel (7 Examples)


4. VBA UsedRange Property for an Inactive Workbook

You can even use the UsedRange property for a workbook that’s not active. Just put the name of the workbook in front.

For example, here my active workbook is Workbook1. To exercise the UsedRange property over Sheet1 of Workbook2, we have to use:

Dim Rng As Range

Set Rng = Workbooks("Workbook2.xlsm").Worksheets("Sheet1").UsedRange

VBA Code to Use the UsedRange Property of VBA

It’ll select the used range of the worksheet Sheet1 of Workbook2.

Read More: VBA for Each Cell in Range in Excel (3 Methods)


Things to Remember

The UsedRange property of VBA returns a Range object. Here in this article, we’ve used the Select property of a Range object for the sake of visualization. But obviously, you can use any other property of a Range according to your convenience.


Conclusion

Using these methods, you can use the UsedRange property in Excel. Do you have any questions? Feel free to ask us.


Related Articles

Tags:

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