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
In VBA you can use various methods to find the last non-empty row in a specific column of an Excel worksheet. Here's how you can achieve this:
- 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.
Detailed example
Here is a detailed example of how you can find the last non-empty row in a specific column of an Excel worksheet:
FindLastNonEmptyRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim columnNumber As Long
' Set the worksheet and column number to search
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your worksheet name
columnNumber = 1 ' Change to the desired column number
' Find the last non-empty row in the specified column
lastRow = ws.Cells(ws.Rows.Count, columnNumber).End(xlUp).Row
' Display the result
MsgBox "The last non-empty row in column " & columnNumber & " is: " & lastRow