CODEWITHSUNDEEP
c#excelrow
eMi
eMi

Reputation: 5618

C# - Excel: Delete Row, depending on value in first Column

Imagine, that u have the Column A. There are 100 Rows and in the Cells are Numbers, like 1, 2, 3 until 100..

How can I programmaticaly (C#) Delete a specific Row, by Example: Deleting the Row which Value in Column A is 5..

I'm working with the Microsoft.Office.Interop.Excel and thats the related code:

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\fre\Desktop\TestDatei.xls");
        Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

        xlWorksheet.Select(Type.Missing);
        Microsoft.Office.Interop.Excel.Range range = xlWorksheet.get_Range("B1:B5", Type.Missing);
        range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);

Upvotes: 1

Views: 14598

Answers (2)

Peter Jeyapaul
Peter Jeyapaul

Reputation: 1

If you want to delete the entire row, try this it works

Range usedRanage=sheet.UsedRange;
foreach (Range r in usedRanage) 
{
    if (Convert.ToString(r.Value2)=="RETRIEVE") 
    {
        r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
    }
}

Upvotes: 0

schwebbe
schwebbe

Reputation: 100

because i was interested, too, i did some investigation on the web and created some sample code. maybee this can help you:

    private void DeleteCells(object sender, EventArgs e)
    {
        // create excel-instance:
        Excel.Application excel = new Excel.Application();
        // open the concrete file:
        Excel.Workbook excelWorkbook = excel.Workbooks.Open(@"D:\test.xls");
        // select worksheet. NOT zero-based!!:
        Excel._Worksheet excelWorkbookWorksheet = excelWorkbook.Sheets[1];
        // create a range:
        Excel.Range usedRange = excelWorkbookWorksheet.UsedRange;

        // iterate range
        foreach (Excel.Range r in usedRange)
        {
            // check condition:
            if (r.Value2 == 5.0F)
                // if match, delete and shift remaining cells up:
                r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        }

        // save changes (!!):
        excelWorkbook.Save();

        // cleanup:
        if (excel != null)
        {
            Process[] pProcess;
            pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
            pProcess[0].Kill();
        }
    }

greetings!

jens

Upvotes: 1

Related Questions