Excel VBA to Find the Last Used Row or Column

UsedRange used wisely

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.

Added 17/11/2021

Rick Rothstein Excel MVP, in the comments below, demonstrated using the Find statement to identify the last used row and column.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Excel VBA to Find the Last Used Row or Column

  1. You can also get the last used row and last used column on a sheet this way…

    LastUsedRow = Cells.Find(“*”, , xlValues, , xlRows, xlPrevious).Row

    LastUsedCol = Cells.Find(“*”, , xlValues, , xlByColumns, xlPrevious).Column

    Note that these code lines will return the row and column for the DISPLAYED values whether those values are constants or from formulas. If you could have formulas past the last displayed values that are returning the empty string (“”) and you want to locate the last row or column for them, just change the xlValues constant in my code lines to xlFormulas.