0
Thanks

A few words of thanks would be greatly appreciated.

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

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
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).