Delete entire row in excel based on value C#

Question

0.00/5 (No votes)

See more:

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 first click:
https://i.stack.imgur.com/cEukZ.png[^]
desired output:
https://i.stack.imgur.com/cEukZ.png[^]

What I have tried:

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

Posted 15-Jan-21 2:08am

Member 14898617

Updated 15-Jan-21 2:41am

Add a Solution

Comments

[no name] 15-Jan-21 8:10am

Why do people think Excel is a suitable "database"?

Member 14898617 15-Jan-21 8:24am

hi thank you your response ..i am creating VSTO for excel so i have no other options..could you please help me with the solution

2 solutions

Solution 1

While Gerry is right: Excel isn't a database, and using it as such will always give you problems when you try to do anything complicated; that isn't the root cause of your problem.

Think about what you are doing: checking each row in a range starting from the top and working your way down. When you find a suitable candidate, you delete a row.
What happens if first candidate is row three?
Row 1 is fine, it stays - check the next row, row 2.
Row 2 is fine, it stays - check the next row, row 3.
Row 3 is a problem, delete it - check the next row, row 4.
But you just deleted row 3, so row 4 is now the third row. So Row 4 contains the data that was on Row 5 and you never process row 4 at all.

As a result, it deletes alternate rows (if you are lucky).

Try going from the end to the beginning and see what happens then.

See what we mean by "Excel is not a database"?

Posted 15-Jan-21 2:31am

OriginalGriff

Comments

CHill60 15-Jan-21 8:42am

Took me so long to type mine, I didn't see yours. At least we agree on what the problem is :laugh:

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2021-01-15T02:41:00

I am not going to follow those links while I am at work. There is very rarely a need for an "image" with problems like this - just put the data into the body of your question - it has the advantage of making it easier for us to paste into a test scenario which makes it easier for us to help you.

However, in this case I can tell you, regardless of your data, that you should start at the end of the range and work backwards.

Demonstration - imagine you have 5 rows of data

Row 1 : A
Row 2 : B
Row 3 : C
Row 4 : D
Row 5 : E

Remember, this is Excel, so Row 1, Row 2, etc is derived for you. You have no control over the row number.

Now you want to delete the rows containing 'C' and 'D' ... currently Rows 3 & 4. Result of deleting 'C' is

Row 1 : A
Row 2 : B
Row 3 : D
Row 4 : E

Note the row numbering has changed. But in your foreach loop, it still thinks that the original numbering holds (it doesn't go back and look at all the data after each deletion - that is determined up front). So the "next" row after what did hold 'C', which was row 4, is now row 3. 'C' was on row 3, we're moving on, so your code next looks at Row 4, which contains E, and D is completely skipped over.

If you do the same thing backwards - start at row 5, delete 'D'.

Row 1 : A
Row 2 : B
Row 3 : C
Row 4 : E

The "next" row examined is still the original Row 3 - the only rows that move are ones that we have already examined and moved past.

Another point to note is that in many languages you cannot change what a foreach is using - i.e. the range for r is fixed. To overcome that use the row numbers instead, so my loop would look something like (untested)

C#

for (int i=5; i>0; i--)
{
     Excel.Range r = excelApp.get_Range(activeworksheet.Cells[i,1]);
     if (r.Value2 > 0.0F)
     {
          // if match, delete and shift remaining cells up:
          r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
     }
}