CODEWITHSUNDEEP
c#excel
Ollie2619
Ollie2619

Reputation: 1295

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

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

Upvotes: 1

Views: 5679

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8726

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

Upvotes: 2

Related Questions