VBA - Last Non-empty row [All versions]

March 2017


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.

Related


Published 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 (ccm.net).