Programmatically getting the last filled excel row using C#
Couple ways,
using Excel = Microsoft.Office.Interop.Excel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Application app = excel.Application;
Excel.Range all = app.get_Range("A1:H10", Type.Missing);
OR
Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
This is a common issue in Excel.
Here is some C# code:
// Find the last real row
nInLastRow = oSheet.Cells.Find("*",System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;
// Find the last real column
nInLastCol = oSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value,System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;
found here
or using SpecialCells
Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);
[EDIT] Similar threads:
- VB.NET - Reading ENTIRE content of an excel file
- How to get the range of occupied cells in excel sheet
Pryank's answer is what worked closest for me. I added a little bit towards the end (.Row
) so I am not just returning a range
, but an integer
.
int lastRow = wkSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;