How to count the number of rows in excel with data?

Question

column A has data like this (ie frequent blank cells):

HEADING  <-- this is A1
kfdsl
fdjgnm
fdkj

gdfkj
4353

fdjk  <-- this is A9

I would like to be able to get the cell reference of the last cell that has data. So in the above example, I want to return: A9

I have tried this but it stops at the first blank cell (ie returning A4)

numofrows = destsheet.Range("A2").End(xlDown).Row - 1

Solution 1

I like this way:

ActiveSheet.UsedRange.Rows.Count

The same can be done with columns count. For me, always work. But, if you have data in another column, the code above will consider them too, because the code is looking for all cell range in the sheet.

Solution 2

Safest option is

Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol =  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

---

Don't use UsedRange or SpecialCells(xlLastCell) or End(xlUp). All these methods may give wrong results if you previously deleted some rows. Excel still counts these invisible cells.

These methods will work again if you delete your cells, save the workbook, close and re-open it.

Solution 3

This will work, independent of Excel version (2003, 2007, 2010). The first has 65536 rows in a sheet, while the latter two have a million rows or so. Sheet1.Rows.Count returns this number dependent on the version.

numofrows = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

or the equivalent but shorter

numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp)

This searches up from the bottom of column A for the first non-empty cell, and gets its row number.

This also works if you have data that go further down in other columns. So for instance, if you take your example data and also write something in cell FY4763, the above will still correctly return 9 (not 4763, which any method involving the UsedRange property would incorrectly return).

Note that really, if you want the cell reference, you should just use the following. You don't have to first get the row number, and then build the cell reference.

Set rngLastCell = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)

Note that this method fails in certain edge cases:

• Last row contains data
• Last row(s) are hidden or filtered out

So watch out if you're planning to use row 1,048,576 for these things!

Solution 4

I compared all possibilities with a long test sheet:

0,140625 sec for

lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row

0 sec for

iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row

and

numofrows = calcws.Cells.SpecialCells(xlLastCell).row

0,0078125 sec for

lastrow = calcws.UsedRange.rows.count
Do While 1
    If calcws.Cells(lastrow, 1).Value = "" Then
        lastrow = lastrow - 1
    Else
        Exit Do
    End If
Loop

I think the favourites are obvious...

Solution 5

Dim RowNumber As Integer
RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row

In your case it should return #9

View more solutions

Comments

[brettdj]

possible duplicate of How can I find last row that contains data in the Excel sheet with a macro?

[Ronnie Royston]

Dim lastRow As Long Dim ws As Worksheet Set ws = Application.ActiveSheet With ws If WorksheetFunction.CountA(Cells) > 0 Then lastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End If End With

[Jean-François Corbett]

This is wrong on a couple of levels. Your first suggestion, .UsedRange.Rows.count, will return the number of rows in the UsedRange, which is not the same thing as the row number of the last piece of data. So if your rows 1 and 2 are empty, this will return the wrong answer by 2. Also, this includes the last non-empty cell on the entire sheet, not just the column under consideration. Maybe that's what the OP wants, but I don't really think so; plus, it's really susceptible to error if someone accidentally writes something in cell "FY54239".

[Jean-François Corbett]

Your second suggestion, destsheet.SpecialCells(xlLastCell).row doesn't even compile, at least in Excel 2003: .SpecialCells doesn't apply to the Sheet object.

[Hari Seldon]

@Jean... It is actually destsheet.Cells.SpecialCells(xlLastCell).Row ... That is definitely my bad for not including that. To your first point the .SpecialCells(xlLastCell).Row can be applied to UsedRange as well. Ive edited my response with working code.

[thornomad]

This worked for me where ActiveSheet.UsedRange.Rows.Count has failed (which is what I usually use).

[Our Man in Bananas]

+1 , and for Excel 2007 onwards, use range("A" & activesheet.rows.count).end(xlup).row :)

[Floris]

If you have no data in row 1, this will give the wrong answer - it will give the number of rows from the first to last cell. If you have data in cells a2:a4 this equation will result in 3, not 4 (using Office 2010).

[Floris]

+1 - this is my favorite answer. Not taking sides, just showing some of the options and their relative efficiency. Should have far more upvotes!

[lcrmorin]

This method may be dangerous because it can returns a cell with color but no data or even a cell wich has contained data but as not been cleaned up properly.

[Graham Anderson]

+1 did a little testing and this does seem to be the most reliable especially if you don't know which column the data in the last row is

[Charles Williams]

This would not work for the original question because the data is not all contiguous.

[Charles Williams]

This is the only safe method to find the last cell containing data. Other methods fail with hidden rows/columns or cells with no data but formatting or cells that have been deleted.

[Andrew Magerman]

Charles, you are so right. In the case shown, my method would indeed fail.

[brettdj]

Should though use Set on a range,and test for that range to exist. This presumes data is present in the sheet - it will fail on a blank sheet

[Jean-François Corbett]

@Floris: Do you think speed is the most important criterion by which to judge which of various possibilities should be favourites? Rather than which works reliably? (Because they don't all return the same results in all circumstances...)

[Floris]

@jeanfrancoiscorbett - obviously getting the right answer matters most. I was commenting on the impartiality of this answer - an objective comparison of speeds . I liked that, and that is what I tried to say in my answer. I can see many ways in which things can go wrong (for example when the last row has a value in it...)

[brettdj]

-1 I don't see a compilation of other codes as times as an answer, especially one that implies time (for an unspecified test) is more important that the actual answer reliability. Lastly numofrows = calcws.Cells.SpecialCells(xlLastCell).row applies to a sheet, not column A

[Jean-François Corbett]

This answer is wrong and should be deleted. .UsedRange.Rows.Count returns the number of rows in the UsedRange, which is not the same thing as the row number of the last piece of data. So if your rows 1 and 2 are empty, this will return the wrong answer by 2.

[haakonlu]

So if I understand things correctly, I could use the "lastrow" variable for setting the first row to start pasting new data "Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row" For example; "rnum = Lastrow +1" (This will be the first empty cell in the sheet(?))

[equalizer]

You have a missing .Row on the great answer above. New users may be confused by the missing statement. numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp) as typed will provide value of last used cell. Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row with the .Row at end will provide the row # of last cell in col 1 as intended.

[Solomon Duskis]

Indeed, performance comparison is useful, but the fact that all the faster methods return incorrect results under certain circumstances is not. In particular, the 4th snippet wrongly assumes that calcws.UsedRange.rows.count is the last row, but if the first few rows of the sheet are empty this assumption doesn't hold.

[Solomon Duskis]

If you want to get the last row in the used range, do it properly -- see newguy's answer. Read sancho.s answer to know what this returns and if you need a row that has data (and not some left-over formatting) and/or in a specific column, see my solution.