Excel Tip – VBA Find the last row in a table

When writing a macro more often than not you will need to know the location of the final row in a table.  As most data tables are not a fixed length you can use this code for the macro to identify the row number.
Lastrow = Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

This code creates a variable “Lastrow” which stores the number of the final row of your table (eg 2673), it searches columns (the range) A:F – Change this to the columns of your table.

This will then search these columns for the last cell that contains data and return the row number of the cell.

The advantage of this method using the range of A:F (or your table range) is that you can ensure that you always get the last row, even if column A has blanks in it.

You could then use the last row variable in the macro in place of the row number, for example

Range("A1:F" & Lastrow).Select

The above code would select my table A1 – F and the final row.

Instead of

Range("A1:F2673").Select

2673″ is replaced with – " & Lastrow

Hope you found this a helpful tip, leave a comment if so, what Excel tricks do you rely on?

One Comment

Add a Comment

Your e-mail address will not be published. Required fields are marked *

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