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).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!