How to delete all rows from excel except header in c#

Question

How do I delete all rows in an excel spreadsheet except the header in c#?

I am trying to do this using the Microsoft.Office.Interop.Excel; library

I now have this code

        Range xlRange = ws.UsedRange;
        int rows = xlRange.Rows.Count;

        Console.WriteLine(rows);

        for (int i = 2; i <= rows; i++)
        {
            ((Range)ws.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
        }

But it's not deleting all the rows, I think because it's deleting the rows when it hits a certain number that row is no longer there, what have I done wrong?

I managed to do it, I have started from the bottom instead of the top, so now my loop is

        for (int i = rows; i != 1; i--)
        {
            ((Range)ws.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
        }

Solution

var range = (Range)ws.Range[ws.Cells[2, 1], ws.Cells[ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count]];
range.Delete(XlDeleteShiftDirection.xlShiftUp);

There had been a change in v4 of .Net that using get_Range stopped working

what have you tried? do you plan do use a particular library, or office automation? — Cee McSharpface, Mar 07 '17 at 11:47
It depends on method you've choosen to access Excel file data. — Andrey Korneyev, Mar 07 '17 at 11:47
I have edited my question to show what library I want to use — Ollie2619, Mar 07 '17 at 11:57
Possible duplicate of [deleting rows from an excel file using c#](http://stackoverflow.com/questions/9951188/deleting-rows-from-an-excel-file-using-c-sharp): you can use the top answer. just omit the condition on the cell content, and let the loop index start at 2 to skip the top row. or construct a range from A2. — Cee McSharpface, Mar 07 '17 at 11:59

score 2 · Accepted Answer · answered Mar 07 '17 at 12:40

If you were working in Excel, you would keep hitting delete on the second row, and observe the rows below shifting up, replacing the cells that were previously occupied by the deleted row.

To replicate that behavior in automation:

for (int i = 2; i <= rows; i++)
{
    ((Range)ws.Rows[2]).Delete(XlDeleteShiftDirection.xlShiftUp);
}

Note that you can also construct a range up front, and delete that without a loop, which will be much faster:

var range = (Range)ws.get_Range(
    ws.Cells[1,2],
    ws.Cells[ws.UsedRange.Cols.Count,ws.UsedRange.Rows.Count]
);
range.Delete(XlDeleteShiftDirection.xlShiftUp);

I had to use the 1st way as the 2nd was giving me `Unhandled Exception: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'object' does not contai n a definition for 'get_Range'` — Ollie2619, Mar 07 '17 at 14:37

How to delete all rows from excel except header in c#

1 Answers1