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

Question

column A has such data (i.e. frequent empty cells):

HEADING <-- this is A1 kfdsl fdjgnm fdkj gdfkj 4353 fdjk <-- this is A9 

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

I tried this, but it stops in the first empty cell (i.e. returns A4 )

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

Answer 1

I like this way:

ActiveSheet.UsedRange.Rows.Count

The same thing can be done when counting columns. I always work for me. But, if you have data in other columns, the code above will also consider them, because the code looks for the entire range of cells in the sheet.

Answer 2

The safest option

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

---

Do not use UsedRange or SpecialCells(xlLastCell) or End(xlUp) . All of these methods may produce incorrect results if you have previously deleted multiple lines. Excel still counts these invisible cells.

These methods will work again if you delete your cells, save the book, close it and reopen it.

Answer 3

This will work regardless of the version of Excel (2003, 2007, 2010). The first has 65,536 rows per sheet, and the last two have a million rows or so. Sheet1.Rows.Count returns this number depending on the version.

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

or equivalent but shorter

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

This looks for the bottom of column A for the first non-empty cell and gets the row number.

This also works if you have data that goes further in other columns. So, for example, if you take the data of your example and also write something in cell FY4763, the above will still correctly return 9 (not 4763 that any method that includes the UsedRange property will be incorrectly returned).

Note that indeed, if you need a cell reference, you should simply use the following. You do not need to get the line number first and then create a link to the cell.

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

Please note that this method does not work in some cases:

• The last line contains data
• Last lines (lines) are hidden or filtered

So stay tuned for whether you plan to use line 1,048,576 for these things!

Answer 4

I compared all the features with a long test sheet:

0.140625 s 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 favorites are obvious ...

Answer 5

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

In your case, it should return # 9

Answer 6

Found this approach on another site. It works with the new large Excel sizes and does not require hard coding of the maximum number of rows and columns.

 iLastRow = Cells(Rows.Count, "a").End(xlUp).Row iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column 

Thanks to mudraker in Melbourne, Australia

Answer 7

They will both work, allowing Excel to determine the last time it sees data.

 numofrows = destsheet.UsedRange.SpecialCells(xlLastCell).row numofrows = destsheet.Cells.SpecialCells(xlLastCell).row 

Answer 8

  n = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count 

Answer 9

I prefer to use the CurrentRegion property, which is equivalent to Ctrl- *, which extends the current range to its largest continuous range with data. You start with a cell or range that you know will contain data, then expand it. The UsedRange property sometimes returns huge areas, simply because someone did formatting at the bottom of the sheet.

 Dim Liste As Worksheet Set Liste = wb.Worksheets("B Leistungen (Liste)") Dim longlastrow As Long longlastrow = Liste.Range(Liste.Cells(4, 1), Liste.Cells(6, 3)).CurrentRegion.Rows.Count 

Answer 10

For clarity, I want to add a clear example and run

  openFileDialog1.FileName = "Select File"; openFileDialog1.DefaultExt = ".xls"; openFileDialog1.Filter = "Excel documents (.xls)|*.xls"; DialogResult result = openFileDialog1.ShowDialog(); if (result==DialogResult.OK) { string filename = openFileDialog1.FileName; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlApp.Visible = false; xlApp.DisplayAlerts = false; xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); var numRows = xlWorkSheet.Range["A1"].Offset[xlWorkSheet.Rows.Count - 1, 0].End[Excel.XlDirection.xlUp].Row; MessageBox.Show("Number of max row is : "+ numRows.ToString()); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } 

Answer 11

Jean-Francois Corbett has no .Row for the big answer above. New users may be confused by the absent operator.

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

as indicated, will provide the value of the last used cell.

 `Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row` 

c .Row at the end will contain the row # of the last cell in column 1, as Jean suggested.