Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello I have an excel file, and there is a column that has either "Y" or "N" for each row and I would like to delete the row if it contains "N" but struggling with the conditional to check it.

What I have tried:

<pre> Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open("C:\\Data\\Enrolled.xlsx");

            Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
            int i = 0;
            for (i = 1; i <= lastRow; i++)
            {
               // Conditional Needed to check if column row has a "N"
               if (excel.WorksheetFunction)
                    (x.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete();

            }


            sheet.Close(true, Type.Missing, Type.Missing);
            excel.Quit();
Posted
Updated 1-May-18 5:25am
Comments
Paulo Zemek 1-May-18 19:29pm    
I am not sure about dealing with excel, but when you delete a row, all other row indexes above that one will change.
So, if you delete row 10, row 11 becomes 10, but your for is still going to check for row 11 (and worst, if the 11 was before the last row, now it is past-last).

So, it will probably help to start from the end:
for (i = lastRow; i>=1; i--) { /* your code */ }

1 solution

You can use range to point a certain cell when loping the worksheet and investigate Range.Value property (Microsoft.Office.Interop.Excel)[^] to check the value.

Something like
C#
...
if (x.Range["A" + i].Value == "N") {
...
 
Share this answer
 
v2
Comments
Maciej Los 1-May-18 14:20pm    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900