Excel: Get TotalRows with VBA

Posted on 2011-10-17
Medium Priority
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

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 600 total points
ID: 36983776


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

cheers, teylyn

Accepted Solution

pritamdutt earned 1200 total points
ID: 36983819

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 42

Assisted Solution

dlmille earned 200 total points
ID: 36983821
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

ID: 36983890
Brilliant !.. a quick solution from you guys !

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

807 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