0

I am trying to use Microsoft.Office.Interop.Excel to get the row number of my excel file, which is 40424 rows.

I have tried codes below:

Debug Infos

They only returns 38304, which is not correct. What is the correct way to get the right row number?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Eddy Lin
  • 475
  • 2
  • 6
  • 19
  • Try this: `int lastRow = old_sheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row` – bzimor Nov 19 '16 at 10:42
  • I have just tried that, same return. – Eddy Lin Nov 19 '16 at 10:45
  • I have also tried: sheet.UsedRange.Row + sheet.UsedRange.Rows.Count - 1, I don't know whether it's relate to my excel file, because when I use it on other files, It' correct. – Eddy Lin Nov 19 '16 at 10:46
  • Did you changed `int` to `long`? – bzimor Nov 19 '16 at 10:50
  • Still the same. – Eddy Lin Nov 19 '16 at 10:52
  • You don't happen to have an old copy of the file (one which had 38304 rows) open in excel? – sgmoore Nov 19 '16 at 11:08
  • Check 38305-row, there may be something different – bzimor Nov 19 '16 at 11:19
  • I can not see why you are not getting the correct number of rows... just guessing... are you sure you have the correct sheet? Are there other sheets in the workbook? – JohnG Nov 19 '16 at 11:53
  • `book.Sheets[1]`. Is that accessing the second sheet? – sgmoore Nov 19 '16 at 14:44
  • 1
    If nothing works, upload your file to dropbox/G-Drive/skydrive and see if we can have a look. Something is clearly amiss. – Hambone Nov 19 '16 at 15:32
  • if you have a non nullable column you can try this workaround `app.WorksheetFunction.CountA(sheet.Cells(1, intCol).EntireColumn)` – Hadi Nov 19 '16 at 19:26
  • Empty rows can cause this issue. take a look at this [Question](http://stackoverflow.com/questions/40574084/remove-empty-rows-and-columns-from-excel-files-using-interop) – Hadi Nov 19 '16 at 19:27
  • Thank you all, I have found the problem, there is actualy an hiden sheet which which is 38304 rows. - -! @sgmoore – Eddy Lin Nov 21 '16 at 01:25

1 Answers1

1

After I try: enter image description here Hide or show worksheets or workbooks

I found there is actualy an hiden sheet. - -!

Thank you all guys, problem solved.

Eddy Lin
  • 475
  • 2
  • 6
  • 19