How to determine the last Row used in VBA including blank spaces in between [duplicate]

How can I determine the last row in an Excel sheet, including some blank lines in the middle?

With this function:

Function ultimaFilaBlanco(col As String) As Long        
        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
        End With            
        ultimaFilaBlanco = lastRow          
End Function

And this data:

Row 1 : Value
Row 2 : Value
Row 3 : Value
Row 4 : Value
Row 5 : Value
Row 6 : White
Row 7 : Value
Row 8 : Value
Row 9 : Value
Row 10  : White
Row 11  : White
Row 12  : White
Row 13  : Value
Row 14  : White

The function returns 5, and I need 13. Any idea how to do it?

Solution 1:

The best way to get number of last row used is:

ActiveSheet.UsedRange.Rows.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Rows.Count for a specific sheet.

To get number of the last column used:

ActiveSheet.UsedRange.Columns.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Columns.Count for a specific sheet.

I hope this helps.

Abdo

Solution 2:

ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row -1

Short. Safe. Fast. Will return the last non-empty row even if there are blank lines on top of the sheet, or anywhere else. Works also for an empty sheet (Excel reports 1 used row on an empty sheet so the expression will be 1). Tested and working on Excel 2002 and Excel 2010.