How delete excel rows based on a value in C# interop is not working

Question

Hi, I have to delete some excel rows based on on cell value .I tried diffrent code and following too. But i get the error.I am using excel Interoperability

"Cannot perform Run time binding on a null refference"

All delete method i am getting same error.I dont understand what mistake i have done here

I have only 3 rows including heading. "DepDel " coloumn have value even other cells are optional and some of them are null

 for (int i = 2; i <= Rows; i++)

      {

        var DepDel1 = (Excel.Range)xlWorksheetNew.Cells[i, 9];
        string DepDel = DepDel1.Value2.ToString();

        Excel.Range r = xlWorksheetNew.Range[xlWorksheetNew.Cells[i, 1], xlWorksheetNew.Cells[i, 10]];

        if (DepDel == "01")
        {
            // if match, delete and shift remaining cells up:
            r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
        }

    }

I was also looking a solution like Autofilter.I couldnt see anything like this(!="somevalue")

          excelRange.AutoFilter(9, cellvalue !="09", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);

Answer 1

I speculate that the cause of the error may be loops.

Suppose there are three rows of data, and the last two rows meet the conditions, then Rows should be 3.

The condition is met when we loop to the second row. After we delete this row, there are only two rows of data left, and the third row becomes the second row, but the loop will make it continue to look for the third row of data, causing an error.

Try to loop in reverse order.

            for (int i = rows; i >1; i--)  
            {  
                var temp = ((Range)worksheet.Cells[i, 2]).Value2;  
                var DepDel = temp.ToString();  
                Range r = worksheet.Range[worksheet.Cells[i, 1], worksheet.Cells[i, 3]];  
                if (DepDel == "1")  
                {  
                    r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);  
                }  
            }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Alternatively, you can use Spire.XLS for .NET to accomplish this task. Below is the code sample for your reference.

using System.Collections.Generic;  
using Spire.Xls;  
  
namespace DeleteSpecificRows  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            //Load Excel document  
            Workbook wb = new Workbook();  
            wb.LoadFromFile(@"C:\Users\Administrator\Desktop\test.xlsx");  
            //Get the first worksheet  
            Worksheet sheet = wb.Worksheets[0];  
            //Find cells containing the specific string  
            CellRange[] textRanges = sheet.FindAllString("E-iceblue", false, false);  
            //Get the row index  
            List<int> rowIndex = new List<int>();  
            foreach (CellRange range in textRanges)  
            {  
                if (!rowIndex.Contains(range.Row))  
                {  
                    rowIndex.Add(range.Row);  
                }  
            }  
            //Delete the corresponding rows  
            for (int i = 0; i < rowIndex.Count; i++)  
            {  
                sheet.DeleteRow(rowIndex[i] - i);  
            }  
            //Save to file  
            wb.SaveToFile("result.xlsx", FileFormat.Version2013);  
        }  
    }  
}