Rifat Hassan
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
⧭ 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").UsedRange4 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.
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
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.
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
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.
Read More: End of a Range Using VBA in Excel (With Examples)
Similar Readings
- Excel VBA Copy Range to Another Sheet (8 Easiest Ways)
- Loop through a Range for Each Cell with Excel VBA (Ultimate Guide)
- Excel Subscript Out of Range Error in VBA (with 5 Solutions)
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
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
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.
