CODEWITHSUNDEEP
c#excelexcel-interop
user2140832
user2140832

Reputation: 41

delete row based on a condition

I am coding in C# and using Microsoft.Office.Interop.Excel. I am have columns A-F and some rows don't have any value in column B. So I want to loop through column B and find all cells with no text/value and then delete that entire row. I manged to loop through column B and find the null cell, but then when I try to delete that row- nothing happens. Here is my code:

Excel.Range B = objsheet.get_Range("B1:B" + lastUsedRow, System.Type.Missing);
foreach (Excel.Range r in B)
{
    string column = r.Text.ToString();

    if (string.IsNullOrEmpty(column))
    {
        Excel.Range BEntireRow = objsheet.get_Range(r + "1:" + r + "B" + lastUsedColumn, System.Type.Missing);
        //  Excel.Range BEntireRow2 = r.EntireRow;
        BEntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);

    }
}

Upvotes: 0

Views: 1614

Answers (2)

Andy G
Andy G

Reputation: 19367

Rather than looping through the column you can do the following; this is the VBA code:

Range("B1:B" + lastUsedRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

In C# you would use:

.SpecialCells(Excel.XlCellType.xlCellTypeBlanks)

BTW I wouldn't name your Excel.Application object Excel - I would use xl or even myXl. It is confusing otherwise, and may cause errors at some point.

Added The full C# statement would be

Range("B1:B" + lastUsedRow).SpecialCells(Excel.XlCellType.xlCellTypeBlanks).EntireRow.Delete();

Upvotes: 2

ATD
ATD

Reputation: 894

Try something like this:

Rows([INSERT_THE_ROW_YOU_WANT_TO_DELETE_HERE]).EntireRow.Delete;

Upvotes: 0

Related Questions