Excel VBA usedRange Property and reset usedRange

This tutorial explains how to use Excel VBA usedRange Property to find the last used row and column number, and reset usedRange.

You may also want to read:

Excel VBA custom Function last row and last column

Excel delete blank rows and blank cells before import into Access

Excel VBA UsedRange Property

Excel VBA UsedRange is a worksheet Property, it returns the area Range bounded by first used cell and last used cell. “Used Cell” is defined as Cell containing formula, formatting, value that has ever been used, even though the value was deleted. In Excel worksheet, we can press Excel shortcut key CTRL+END to select the last used cell.

The picture below highlights the area of UsedRange

Reset UsedRange

UsedRange can be reset using the following Procedure.

Public Sub reset_usedrange()
    a = ActiveSheet.UsedRange.Rows.Count
End Sub

The above procedure work under normal circumstances, but it does not work in some cases where the data source was downloaded from other system. In that case you should use the below procedure to clear all empty cells and then delete the row.

Public Sub delete_empty_row()
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

Find the last used row and column

A popular use of UsedRange Property is to find the last used row and column.

To find the last row number, use the below code

usedRangeLastRow = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

To find the last column number, use the below code

usedRangeLastColNum = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

To convert column number to column letter

usedRangeLastColNm= Split(Cells(1,Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column).Address, "$")(1)

Find the address of usedRange

Address Property returns the absolute address of a Range. In the above example, the below code will return $B$1:$E$4

Activesheet.UsedRange.Address

Afterwards you can use split function to get  the starting or ending Range.

Click here to see how to use Split Function.

Outbound References

https://msdn.microsoft.com/en-us/library/office/aa207501%28v=office.11%29.aspx