Programmatically getting the last filled excel row using C#

Question

I am trying to get the last row of an excel sheet programatically using the Microsoft.interop.Excel Library and C#. I want to do that, because I am charged with looping through all the records of an excel spreadsheet and performing some kind of operation on them. Specifically, I need the actual number of the last row, as I will throw this number into a function. Anybody have any idea how to do that?

Priyank · Accepted Answer · 2011-10-06T13:33:15.357

62

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;

edited Oct 06 '11 at 13:33

answered Oct 06 '11 at 13:08

Priyank

10,503
2
27
25

2

Not sure how the first option would work... But the second one looks interesting. How can I translate that into an int Value? – SoftwareSavant Oct 06 '11 at 13:13
I edited my code, i haven't tested that code but hope that will give you an idea for what you want to achieve. – Priyank Oct 06 '11 at 13:33
12

This answer gives you the last cell in the worksheet's UsedRange. The problem, though, is that UsedRange never contracts. If you have a value in A1 and AA100, then delete the content of AA100, the UsedRange will still be A1:AA100. AA100 will still be considered the last cell. – CtrlDot Aug 28 '12 at 11:35
perfect solution, gave me exactly what I needed – Simon Price Jan 13 '16 at 15:19
3

@CtrlDot is a very valid point, UsedRange does not work as expected and cannot be trusted neither can xlCellTypeLastCell method. – Leo Gurdian May 14 '19 at 19:52

score 20 · Answer 2 · edited May 23 '17 at 11:54

20

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:

edited May 23 '17 at 11:54

Community

1
1

answered Oct 06 '11 at 13:04

JMax

26,109
12
69
88

This is working, I been struggling with getting the correct row. – NWessel Jul 26 '16 at 12:54
2

method 1 works. Method 2 - Unreliable deleted used cells get "tagged" by UsedRange and "xlCellTypeLastCell" as "dirty" and still thinks those cells are still in use and report back empty cells as "used" – Leo Gurdian May 14 '19 at 20:00
1

Note that you may get incorrect answers if filters or other row-hiding effects exist which obfuscate the actual last row of data. A below answer goes over this, but you can also use the answers from [here](https://stackoverflow.com/questions/13204064/turn-off-filters). I used `excelWorksheet.AutoFilter.ShowAllData()` to preserve the filter presence while releasing applied filters so that all rows were shown. – Hydronium Nov 05 '21 at 19:30

score 15 · Answer 3 · edited Jul 12 '17 at 10:38

15

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;

edited Jul 12 '17 at 10:38

prime23

3,362
2
36
52

answered Oct 07 '11 at 11:41

SoftwareSavant

9,467
27
121
195

Saved my day! Thanks! – krobelusmeetsyndra Apr 08 '19 at 11:41
3

Unreliable deleted used cells get "tagged" by UsedRange and "xlCellTypeLastCell" as "dirty" and still thinks those cells are still in use and report back empty cells as "used" – Leo Gurdian May 14 '19 at 19:55

Gerhard Powell · Answer 4 · 2014-03-24T16:28:30.770

The only way I could get it to work in ALL scenarios (except Protected sheets):

It supports:

Scanning Hidden Row / Columns
Ignores formatted cells with no data / formula

Code:

// Unhide All Cells and clear formats
sheet.Columns.ClearFormats();
sheet.Rows.ClearFormats();

// Detect Last used Row - Ignore cells that contains formulas that result in blank values
int lastRowIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                InteropExcel.XlFindLookIn.xlValues,
                InteropExcel.XlLookAt.xlWhole,
                InteropExcel.XlSearchOrder.xlByRows,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Row;
// Detect Last Used Column  - Ignore cells that contains formulas that result in blank values
int lastColIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                InteropExcel.XlSearchOrder.xlByColumns,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Column;

// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
int lastColIncludeFormulas = sheet.UsedRange.Columns.Count;
int lastColIncludeFormulas = sheet.UsedRange.Rows.Count;

Brilliant answer (and the only one which worked for me). – Mike Gledhill Oct 30 '15 at 12:29 — Mike Gledhill, Oct 30 '15 at 12:29

score 10 · Answer 5 · answered Oct 06 '11 at 13:11

10

For questions involving the Excel object model, it's often easier to try it out in VBA first, then translating to C# is fairly trivial.

In this case one way to do it in VBA is:

Worksheet.UsedRange.Row + Worksheet.UsedRange.Rows.Count - 1

answered Oct 06 '11 at 13:11

Joe

122,218
32
205
338

score 2 · Answer 6 · edited Sep 09 '19 at 15:05

The ActiveSheet.UsedRange.Value returns a 2 dimensional object array of [row, column]. Checking the length of both dimensions will provide the LastRow index and the LastColumn index. The example below is using C#.

Excel.Worksheet activeSheet;
Excel.Range activeRange;

public virtual object[,] RangeArray 
{
    get { return ActiveRange.Value; }
}

public virtual int ColumnCount 
{
    get { return RangeArray.GetLength(1); }
}

public virtual int RowCount
{
    get { return RangeArray.GetLength(0); }
}

public virtual int LastRow 
{
    get { return RowCount; }
}

Norbert Tóth · Answer 7 · 2016-02-23T08:58:53.080

This issue is even worse when there are possibly empty cells. But you have to read a row even if only one value is filled. It can take a while when there are a lot of unfilled cells but if the input is close to correct it is rather fast.

My solution ignores completely empty rows and returns the longest column's row count:

private static int GetLastRow(Worksheet worksheet)
    {
        int lastUsedRow = 1;
        Range range = worksheet.UsedRange;
        for (int i = 1; i < range.Columns.Count; i++)
        {
            int lastRow = range.Rows.Count;
            for (int j = range.Rows.Count; j > 0; j--)
            {
                if (lastUsedRow < lastRow)
                {
                    lastRow = j;
                    if (!String.IsNullOrWhiteSpace(Convert.ToString((worksheet.Cells[j, i] as Range).Value)))
                    {
                        if (lastUsedRow < lastRow)
                            lastUsedRow = lastRow;
                        if (lastUsedRow == range.Rows.Count)
                            return lastUsedRow - 1;
                        break;
                    }
                }
                else
                    break;
            }
        }
        return lastUsedRow;
    }

score 1 · Answer 8 · answered Jan 31 '23 at 16:35

In case of using OfficeOpenXml nowadays:

using OfficeOpenXml;
using System.IO;

FileInfo excelFile = new FileInfo(filename);
ExcelPackage package = new ExcelPackage(excelFile);
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
int lastRow = sheet.Dimension.End.Row;
int lastColumn = sheet.Dimension.End.Column;

I don't know if using Microsoft.Office.Interop.Excel is still state of the art or more a legacy library. In my opinion I'm doing well replacing with OfficeOpenXml. So this answer might be usefull for future search results.

score 0 · Answer 9 · answered Dec 11 '17 at 07:41

For those who use SpecialCells method, (I'm not sure about others), Please Note in case your last cell is merged, you won't be able to get last row and column number using Range.Row and Range.Column to get the last row and column as numbers. you need to first Unmerge your range and then Again get the last cell. It cost me a lot.

private int[] GetLastRowCol(Ex.Worksheet ws)
    {
        Ex.Range last = ws.Cells.SpecialCells(Ex.XlCellType.xlCellTypeLastCell, Type.Missing);
        bool isMerged = (bool)last.MergeCells;
        if (isMerged)
        {
            last.UnMerge();
            last = ws.Cells.SpecialCells(Ex.XlCellType.xlCellTypeLastCell, Type.Missing);
        }
        return new int[2] { last.Row, last.Column };
    }

score 0 · Answer 10 · answered Feb 05 '19 at 08:52

As previously discussed, the techniques above (xlCellTypeLastCell etc.) do not always provide expected results. Although it's not difficult to iterate down through a column checking for values, sometimes you may find that there are empty cells or rows with data that you want to consider in subsequent rows. When using Excel directly, a good way of finding the last row is to press CTRL + Down Arrow a couple of times (you'll end up at row 1048576 for an XLSX worksheet) and then press CTRL + Up Arrow which will select the last populated cell. If you do this within Excel while recording a Macro you'll get the code to replicate this, and then it's just a case of tweaking it for C# using the Microsoft.Office.Interop.Excel libraries. For example:

    private int GetLastRow()
    {
        Excel.Application ExcelApp;
        ExcelApp = new Excel.Application();

        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();

        ExcelApp.Selection.End(Excel.XlDirection.xlUp).Select();

        return ExcelApp.ActiveCell.Row;
    }

It may not be the most elegant solution (I guess instead you could navigate to the final row within the spreadsheet first directly before using XlUp) but it seems to be more reliable.

score 0 · Answer 11 · answered Sep 08 '20 at 22:41

As CtrlDot and Leo Guardian says, it is not very acuarate the method, there some files where formats affect the "SpecialCells".

So I used a combination of that plus a While.

Range last = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
Range range = sheet.get_Range("A1", last);
int lastrow = last.Row;
// Complement to confirm that the last row is the last
string textCell= "Existe";
while (textCell != null)
{
 lastrow++;
 textCell = sheet.Cells[lastrow + 1, 1].Value;
}

Programmatically getting the last filled excel row using C#

11 Answers11

Linked