Worksheet.UsedRange property

Returns a Range object that represents the used range on the specified worksheet. Read-only.

Syntax

expression.UsedRange

expression A variable that represents a Worksheet object.

Example

The following example selects the used range on Sheet1.

Worksheets("Sheet1").Activate 
ActiveSheet.UsedRange.Select

When this code is used with the following example table, range A1:F7 will be selected.

The following example clears everything from the used range from the active sheet:

ActiveSheet.UsedRange.Clear

The following example copy the entire used range:

ActiveSheet.UsedRange.Copy

The following example shows a message box with the count of rows and columns that you have in the used range:

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

Activate the last cell from the used range:

With ActiveSheet.UsedRange
    .Cells(.Rows.Count, .Columns.Count).Select
End With

Get the Address of the UsedRange:

MsgBox ActiveSheet.UsedRange.Address

Count Empty Cells from the Used Range:

Sub CountEmpty()
    Dim iCell As Range
    Dim t As Long
    Dim i As Long
    
    For Each iCell In ActiveSheet.UsedRange
        t = t + 1
        If IsEmpty(iCell) = True Then
            i = i + 1
        End If
    Next iCell
    
    MsgBox "There are total " & t & _
        " cell(s) in the range, and out of those " & _
        i & " cell(s) are empty."
End Sub