Find the range of used cells in an Excel worksheet

[nandagopal]

A Worksheet's UsedRange property gives a Range that includes the worksheet's used cells. It is a rectangular range so it includes the maximum and minimum rows and columns that contain anything.

The following code selects the worksheet's UsedRange. It then displays the range's maximum and minimum row and column numbers.

Code Select Expand

Sub ShowUsedRange()
Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer

    ' Select the used range.
    Set sheet = ActiveSheet
    sheet.UsedRange.Select

    ' Display the range's rows and columns.
    row_min = sheet.UsedRange.Row
    row_max = row_min + sheet.UsedRange.Rows.Count - 1
    col_min = sheet.UsedRange.Column
    col_max = col_min + sheet.UsedRange.Columns.Count - 1

    MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
           "Columns: " & col_min & " - " & col_max
End Sub