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
– Cee McSharpface, Commented Mar 7, 2017 at 11:47
It depends on method you've choosen to access Excel file data. — Andrey Korneyev
– Andrey Korneyev, Commented Mar 7, 2017 at 11:47
I have edited my question to show what library I want to use — Ollie2619
– Ollie2619, Commented Mar 7, 2017 at 11:57
Possible duplicate of deleting rows from an excel file using c#: 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
– Cee McSharpface, Commented Mar 7, 2017 at 11:59

Cee McSharpface · Accepted Answer · 2017-03-07 12:40:03Z

2

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);

answered Mar 7, 2017 at 12:40

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ollie2619 Over a year ago

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'

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related