Excel VBA: 6 Ways to Find the Last Row in a List

A very common task in Excel VBA is to find the last row of a list. This can be for many reasons such as to make a chart or PivotTable ranges dynamic, or to append fresh data to the bottom of a list.

This blog post uncovers 6 different ways you can find the last row. In these examples the number of the last row is assigned to a variable for use within your procedures.

Are you new to Excel VBA? Learn Excel VBA quickly with this online course.

For these examples, the following variables have been declared and the sht variable activated.

Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")

Using Ctrl + Down Arrow

You may know that using the keyboard shortcut of Ctrl + the down arrow key will shoot you to the bottom of a list.

The VBA code for the same shortcut is shown below.

This assumes A1 is the first cell of data.

LastRow = Range("A1").End(xlDown).Row

This is a common approach but has its disadvantages. This shortcut finds the bottom of a block of cells. This means that if you have blank cells in a range it will not accurately find the last row.

Using Ctrl + Up Arrow

If you have blanks cells in a range, then you could work around that by starting quite far down a spreadsheet and using the shortcut to go up and hit the bottom (instead of top and down).

LastRow = Range("A100000").End(xlUp).Row

You will need to ensure the starting cell is further than you will ever need. In this example it is row 100,000.

Using the UsedRange Property of VBA

The UsedRange property in Excel VBA is the range of filled cells on a spreadsheet.

It is extremely useful that Excel knows this, and we can use this information on a worksheet by using the shortcuts Ctrl + End and Ctrl + Home.

However this UsedRange needs resetting or it may contain outdated information about the sheet. The code below assigns the last row number of the UsedRange to as variable.

LastRow = sht.UsedRange.Rows.Count

Counting the Values in a Column

The COUNTA function can be used to count all non-blank cells in a range to find the last row.

Now like the first example, you will need to be careful of blank cells in a range causing a problem.

However, in my blog post on creating a macro to remove blank rows from a list, I used this technique for exactly that reason. To find out how big the list is, when the blanks are removed.

LastRow = Application.CountA(Range("A:A"))

Using a Named Range

If you have set up a named range for your list, this can make it easy to find the last row. In the code below the named range of the list is Sales.

LastRow = sht.Range("Sales").Rows.Count

Using a Range Formatted as a Table

If you have already formatted your range as a table. We can used the table to locate the last row number.

LastRow = sht.ListObjects("Table1").Range.Rows.Count

More Excel VBA Tutorials

• Loop Through all the Files in a Folder using VBA
• Count Cells in a Range By their Cell Colour
• Export Multiple Excel Sheets to a Single PDF
• Format Specific Words in a Cell using VBA