VBA - Last Non-empty row [All versions]

March 2018

Between the 2003 and 2007 versions, the number of rows that can be reached in an Excel sheet has changed significantly. As a result, the VBA codes as below ,prevent the portability of your workbook from one version to another:
- Versions <2007:
Dim LastLine As Long 
LastLine = Range("A65536").End(xlUp).Row

- Since 2007:
Dim LastLine As Long 
LastLine = Range("A1048576").End(xlUp).Row

In any case, you should now use [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 syntax):
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.


Published by jak58. Latest update on July 25, 2011 at 07:33 AM by jak58.
This document, titled "VBA - Last Non-empty row [All versions] ," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Visual Basic - Deleting rows in spreadsheet
VBA - Conditional formatting- Apply color to last column