How to use UsedRange Property in VBA in Excel

Home ➜ VBA Tutorial ➜ How to use UsedRange Property in VBA in Excel

In VBA, the UsedRange property represents the range in a worksheet that has data in it. The usedrange starts from the first cell in the worksheet where you have value to the last cell where you have value. Just like the following example where you have used range from A1 to C11.

Note: UsedRange property is a read-only property.

Write a Code with UsedRange

Use the following code.

1. First, you need to specify the worksheet.
2. Then enter a dot (.) and enter “UsedRange”.
3. After that, that use the property or method that you want to use.
4. In the end, run the code.

Sub vba_used_range()
    ActiveSheet.UsedRange.Clear
End Sub

The above code clears everything from the used range from the active sheet.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Copy UsedRange

Use the following code to copy the entier UsedRange.

Sub vba_used_range()

    ActiveSheet.UsedRange.Copy

End Sub

Count Rows and Columns in the UsedRange

There’s a count property that you can use to count rows and columns form the used range.

MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

The above two lines of code shows a message box with the count of rows and columns that you have in the used range.

Activate the Last Cell from the UsedRange

You can also activate the last cell from the used range (that would be the last used cell in the worksheet). Consider the following code.

Sub vba_used_range()

Dim iCol As Long
Dim iRow As Long

iRow = ActiveSheet.UsedRange.Rows.Count
iCol = ActiveSheet.UsedRange.Columns.Count

ActiveSheet.UsedRange.Select
Selection.Cells(iRow, iCol).Select

End Sub

This code takes the rows and columns count using the UsedRange property and then use those counts to select the last cell from the used range.

Refer to UsedRange in a Different WorkSheet

If you are trying to refer to the used range in a worksheet other than the active sheet then VBA will how an error like the following.

So, the worksheet where you are referring to must be activate (only then you can use the UsedRange property).

Sub vba_used_range()

    Worksheets("Sheet4").Activate
    Worksheets("Sheet4").UsedRange.Select

End Sub

That mean you can’t refer to the used range in a workbook that is closed. But you can use open a workbook first and then, activate the worksheet to use the UsedRange property.

Get Address of the UsedRange

Use the following line of code to get the address of the used range.

Sub vba_used_range()

 MsgBox ActiveSheet.UsedRange.Address

End Sub

Count Empty Cells from the Used Range

The following code uses the FOR LOOPS (For Each) and loops through all the cells in the used range and counts cells that are empty.

Sub vba_used_range()

Dim iCell As Range
Dim iRange As Range
Dim c As Long
Dim i As Long

Set iRange = ActiveSheet.UsedRange

For Each iCell In ActiveSheet.UsedRange

    c = c + 1

    If IsEmpty(iCell) = True Then
        i = i + 1
    End If

Next iCell

MsgBox "There are total " & c & _
" cell(s) in the range, and out of those " & _
i & " cell(s) are empty."

End Sub

When you run this code, it shows a message box with the total count of cells and how many cells are empty out of that.