Excel: Get TotalRows with VBA

Posted on 2011-10-17
Last Modified: 2012-06-27
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
Question by:veematics
    LVL 50

    Assisted Solution



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

    cheers, teylyn
    LVL 9

    Accepted Solution


    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!

    LVL 41

    Assisted Solution

    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" ;)


    Author Closing Comment

    Brilliant !.. a quick solution from you guys !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    This article will show you how to use shortcut menus in the Access run-time environment.
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now