delete entire row in excel based on value c#

ravi kumar 331 Reputation points
2021-01-15T13:30:43.623+00:00

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);
                }
            }
        }
Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,699 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,389 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,583 questions
0 comments No comments
{count} votes

Accepted answer
  1. Daniel Zhang-MSFT 9,621 Reputation points
    2021-01-19T08:01:08.96+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Alberto Poblacion 1,556 Reputation points
    2021-01-16T10:20:00.227+00:00

    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.

    0 comments No comments

  2. ravi kumar 331 Reputation points
    2021-01-17T04:48:21.377+00:00

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