question

sreeprasad-9894 avatar image
0 Votes"
sreeprasad-9894 asked Ezreal95-7594 answered

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

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);
dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered TimonYang-MSFT edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ezreal95-7594 avatar image
0 Votes"
Ezreal95-7594 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.