The number of rows in the sheet

Question

Purpose:
The variable must contain the number of rows from a particular sheet.

Problem:
What syntax code in Excel VBA do I need to count the number of rows from a worksheet?

Answer 1

Using the usedrange method is one of my favorites, but it should be handled with care. It has several drawbacks / gotchas. This is a known issue that excel does not track the used range very well. Any reference to the used range through VBA will reset the value of the current range. So try this helper procedure when you get the used range:

Dim lRowCount as Long Application.ActiveSheet.UsedRange lRowCount = Worksheets("MySheet").UsedRange.Rows.Count 

But remember that this will give you the number of ranges used , so if you have blank lines at the top of your book (which often people do to leave room for things like filter criteria, etc.), then they don’t will be taken into account. Formatting may also affect the usedrange method.

If you want to use the last line, which I think you need, then you can use the more reliable find method:

 Dim rLastCell As Range Dim lLastRow As Long Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False) If Not rLastCell Is Nothing Then lLastRow = rLastCell.Row 

If you know that you have at least one cell with data in it, you can simplify this:

 Dim lLastRow As Long lLastRow = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row 

See here for the range used, which I mentioned above

Answer 2

You can also try:

 i = Sheets("SheetName").UsedRange.Rows.Count 

However, if you start deleting and clearing strings, this can be a little tricky.

The best way to do this is:

 i = Cells(Sheets("SheetName").Rows.Count, 1).End(xlup).Row 

This assumes that each row has data in column 1.