Using VBA to find last non empty row: in column, in table

Using VBA to find last non empty row: in column, in table

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.

Any more excel questions? check out our forum!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel