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

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.

Safest option is

Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).RowLastcol =  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.

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!