When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.

VBA (Visual Basic for Applications) has a property that identifies the used range called amazingly UsedRange. See an example below on how to used it.

Set rng = ActiveSheet.UsedRange

This sets the rng variable to the used range for the active sheet.

To find the last used row number in the active sheet you can use something like

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

The variable LastRow will hold the last used row number in the active sheet.

You can’t reliably use the following code

LastRow = ActiveSheet.UsedRange.Rows.Count

Because the sheet entries may not start in row 1. If they did you could use it.

To get the last used column number in the active sheet.

LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

The LastColumn variable will contain the last used column number, not the column letter.

Using numbers is easier when working with columns because you can easily increment and perform calculations with them.