Excel VBA usedRange Property

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

Excel VBA UsedRange Property

Excel VBA UsedRange is a worksheet Property, it returns the area Range bounded by last used column and last used row. Used Cell is defined as Cell containing formula, formatting, value.

The picture below highlights the area of UsedRange

UsedRange is best to find the data Range where some data in the last rows are blank. In our example, even though Cell C4 is blank, it still includes C4 due to format in E4 and data in B4.

Find the last used row and column

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

To find the last row number, use the below code

usedRangeLastRow = Activesheet.UsedRange.Rows.Count

To find the last column number, use the below code

usedRangeLastColNum = Activesheet.UsedRange.Columns.Count

However, it is not recommended to use UsedRange to find the last column and row for the following reasons:

– Formatted Cell without value is considered as used

– Even if you delete the format of a formatted Cell, the Cell is still considered as used

– Because the above formula uses Count Method, data must start in Cell A1

If you want to find the last row and column, use End Property instead. Click here to read more.

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

Outbound References

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

Excel VBA usedRange Property