Excel: Get TotalRows with VBA

Hello i want to get the True Latest Row in Excel using VBA.
At the moment i use this solution:
ActiveSheet.Cells(1, 1).End(xlDown).Row

but the problem is when there's a blank row, the counting stop at the blank row
Who is Participating?
pritamduttConnect With a Mentor Commented:

If I understand correctly, you desire to know that last row that contains data in your worksheet.
And yes you are right the above method would not work if there are blank rows in between.

Here is a method to find the last row used in the excel sheet.

Hope this helps

For ActiveSheet you can use the following Method

LastRowUsed = ActiveSheet.UsedRange.Rows.Count

Open in new window

Hope this helps!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:


ActiveSheet.Cells(Rows.count, "A").End(xlup).Row

cheers, teylyn
dlmilleConnect With a Mentor Commented:
The command


works great, when you know what column will always have the last row.  This, I believe is the most efficient, in that situation.

However, I've recently (last few months) been turned on to what I now believe is the best way to find the true last Row in Excel, without regard to looking at a particular column, is:

dim myLastRow as long

     myLastRow = Cells.Find(what:="*",SearchDirection:=xlPrevious).Row

Its not always needed - but good to have in your "back pocket" ;)

veematicsAuthor Commented:
Brilliant !.. a quick solution from you guys !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.