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.
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.
Thanks again Rick.