How to count the number of rows in excel with data

Question

column A has data like this  ie frequent blank cells    HEADING   lt -- this is A1 kfdsl fdjgnm fdkj  gdfkj 4353  fdjk   lt -- 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

User · Answer

Dim RowNumber As Integer  RowNumber   ActiveSheet Range  A65536   End xlUp  Row  In your case it should return  9

User · Answer

n   ThisWorkbook Worksheets 1  Range  A A   Cells SpecialCells xlCellTypeConstants  Count

User · Answer

These would both work as well  letting Excel define the last time it sees data   numofrows   destsheet UsedRange SpecialCells xlLastCell  row  numofrows   destsheet Cells SpecialCells xlLastCell  row

User · Answer

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

User · Answer

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

User · Answer

Found this approach on another site   It works with the new larger sizes of Excel and doesn t require you to hardcode the max 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 Melborne  Australia

User · Answer

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

User · Answer

For greater clarity  I want to add a clear example and running              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

User · Answer

I prefer using the CurrentRegion property  which is equivalent to Ctrl-   which expands the current range to its largest continuous range with data  You start with a cell  or range  which you know will contain data  then expand it  The UsedRange Property sometimes returns huge areas  just because someone did some 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

User · Answer

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

[.net] How to count the number of rows in excel with data?

Examples related to .net

Examples related to vb.net

Examples related to excel

Examples related to vba

Examples related to excel-interop