Click here to Skip to main content
15,067,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 15-Jan-21 2:41am
Comments
Gerry Schmitz 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

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);
     }
}
   
Comments
Member 14898617 17-Jan-21 0:01am
   
hi thank you so much for your answer , now i am getting below error:
Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: ''object' does not contain a definition for 'get_Range''


at the line :
Excel.Range r = excelApp.get_Range(activeworksheet.Cells[i, 1]);


i searched and tweaked some lines but no use, please help me.
CHill60 18-Jan-21 7:06am
   
Replace the get_Range with whatever you would use to get a single cell given its row number and column number - e.g. Range()
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"?
   
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:

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