delete entire row in excel based on value c#

Question

I am creating add-in in excel to delete the selected range where the values is greater than 0 , but currently if more continuous cells contains greater than 0 values , it is deleting only 1 & 3 values(odd rows) and leaving the rest , kindly guide me how to delete all the rows contains the value more than 0.
first table:
https://i.stack.imgur.com/wbo7c.png
after one click:
https://i.stack.imgur.com/cEukZ.png
desired output:
https://i.stack.imgur.com/D4DNx.png

my code:

private void btncalc_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Worksheet activeworksheet = Globals.ThisAddIn.Application.ActiveSheet;

            Range usedRange = activeworksheet.UsedRange;

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

Answer 1

Hi ravikumar-1532,
You can also try to delete by looping through each row.
Here is my test code example:(The data is located in column A starting A1)

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();  
long i;  
i = 1L;  
Microsoft.Office.Interop.Excel.Workbook ThisWorkbook = excel.Workbooks.Open(@"C:\Users\Desktop\t1.xls");  
while (i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count)  
{  
    short number;  
    bool result = Int16.TryParse(ThisWorkbook.ActiveSheet.Cells(i, 1).Text, out number);  
    if (number ==1)  
    {  
        Console.WriteLine(ThisWorkbook.ActiveSheet.Cells(i, 1).Text);  
        ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete();  
    }  
    else  
    {  
        i = i + 1L;  
    }  
}  
ThisWorkbook.SaveAs(@"C:\Users\Desktop\t2.xls");  

Best Regards,
Daniel Zhang

---

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

The likely reason why it is deleting the odd rows is that the foreach loop is not aware of the deletion. For example, when the loop is in row 1, it deletes the row 1 and goes on to row 2. But now row 2 is not the row that was originally in position 2, because when you deleted row 1 the row 3 went up to occupy the position that was previously occupied by row2. So the loop goes on to delete the row that you think of as row 3 because from its point of view it is now row 2.

If this was running on a .Net managed collection, you would get a runtime error telling you that you cannot modify within a foreach loop the same collection on which the foreach loop is iterating. But in this case the collection is a COM object, which is not as "clever" and it cannot inform you of the error.

One solution is to reverse the loop, i.e., start from the end and loop towards the beginning. However, you cannot do this with "foreach"; you will need to convert it into a "for" so that you can use the index to count down.

Answer 3

hi @Alberto Poblacion thank you so much for your answer ..after now have come up to a point where it is successfully removing the values greater than zero. But it is only deleting only when I select column "A" and if I select any other column it is showing error like below:

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A01A8'  

below is my new code:

using Microsoft.Office.Tools.Ribbon;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using Excel = Microsoft.Office.Interop.Excel;  
using Office = Microsoft.Office.Core;  
using Microsoft.Office.Tools.Excel;  
using Microsoft.Office.Interop.Excel;  
  
namespace CpCpk  
{  
    public partial class Ribbon1  
    {  
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)  
        {  
  
        }  
  
        private void button1_Click(object sender, RibbonControlEventArgs e)  
        {  
            Excel.Worksheet activeworksheet = Globals.ThisAddIn.Application.ActiveSheet;  
            Range usedRange = activeworksheet.UsedRange;  
            List<Range> list = new List<Range>();  
            foreach (Excel.Range r in usedRange)  
            {  
                // check condition:  
                if (r.Value2 > 0.0F)  
                {  
                    list.Add(r);  
                }  
            }  
            list.Reverse();  
            foreach (var r in list)  
            {  
                r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);  
            }  
        }  
    }  
}  

the error is coming at :

    r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);