0
Thanks

A few words of thanks would be greatly appreciated.

VBA - Last Non-empty row [All versions]


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.

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

0 Comments