Skip to main content

Worksheet.UsedRange property

Table of contents
  1. Syntax
  2. Example

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



expression A variable that represents a Worksheet object.


The following example selects the used range on Sheet1.


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:


The following example copy the entire used range:


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

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>