EXCEL VBA LAST USED ROW


Identifying the last row is a task that you will perform frequently inside of Excel VBA. There are a number of methods for trapping the last row of a workbook. This article will explore the methods for trapping and using the last row in your code. The following article draws on the teachings from my topic on dynamic ranges. Dynamic Ranges in Excel


5 Methods For Trapping The Last Row

Trap the last used row in Column A. This method goes to the bottom cell in an office 365 worksheet and comes up to the row that has data. For this example we will use the variable (lr) short for last row and declare the variable as a long integer. For the first 3 methods we will use column A as the column to trap the last row.


  • Method 1

Using the square brackets method of trapping a range is probably the shortest way to create a dynamic range based on a column.

Dim lr As Long
lr = [A1048576].End(xlUp).Row

Method 2

The more common way to declare a variable to trap the last row using a Range reference.

Dim lr As Long
lr = Range("A" & Rows.count).End(xlUp).Row

Method 3

Referring to the last row using the Cells method.

Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

All three methods start at the bottom of column A and come up to the first row with data in it. I would suggest this is better practice than starting at the top and going down as if there are blank rows a false reading will be recorded.


  • Method 4

Choosing column A as the ‘banker’ column may not work in all cases as the data may have a longer column which is not column A. The ultimate trap of the last used row in a worksheet is as follows.

Dim lr As Long
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row

The above works very effectively to trap the last row where any column could have the most data and will return a very nice dynamic last row.


Trapping The Last Row In A Table

  • Method 5

Tables are a bit more tricky as they are an object inside of a worksheet object. This added layer of complexity means you have to trap the table differently.

Dim lr As Long
lr = Sheet1.ListObjects("YOURTABLENAME").Range.Rows.Count

The trick with the above is the worksheet code name Sheet1. Be sure to use the correct worksheet code name.


The more you write code to trap the last row in your workbook the more you will become familiar with what to use and when to use it. The VBA language is wonderfully versatile and you will find your way with time. All the very best with the journey.