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

Question

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?

Answer 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