VBA - Last Non-empty row [All versions]

Between the 2003 and the most recent versions, the number of rows that can be reached in an Excel sheet has changed significantly. As a result, the VBA codes prevent the portability of your workbook from one version to another.

VBA codes

Versions <2007:

Dim LastLine As Long

LastLine = Range("A65536").End(xlUp).Row

Since 2007:

Dim LastLine As Long

LastLine = Range("A1048576").End(xlUp).Row

All versions:

Dim LastLine As Long

LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row

or :

Dim LastLine As Long

LastLine = Range("A" & Rows.Count).End(xlUp).Row

You will also face the same problem when using the columns. You can use this code (given here as an example, there are other syntaxes):

Dim LastCol As Integer

LastCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column

The codes listed here relate to the last non-blank line in column A (Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row , Range("A" & Rows.Count).End(xlUp).Row) and the last column whose first line is not empty (Cells(1, Cells.Columns.Count).End(xlToLeft).Column). Of course you can adapt the code to your convenience.

Photo: © Everypixel