How delete excel rows based on a value in C# interop

Question

1.00/5 (1 vote)

See more:

Excel-2016

Hi, I have to delete some excel rows based on on cell value .I tried diffrent code and following too. But i get the error.I am using excel Interoperability

"Cannot perform Run time binding on a null refference"

All delete method i am getting same error.I dont understand what mistake i have done here

What I have tried:

for (int i = 2; i <= Rows; i++)

        {

            var DepDel1 = (Excel.Range)xlWorksheetNew.Cells[i, 9];
            string DepDel = DepDel1.Value2.ToString();

            Excel.Range r = xlWorksheetNew.Range[xlWorksheetNew.Cells[i, 1], xlWorksheetNew.Cells[i, 10]];

            if (DepDel == "01")
            {
                // if match, delete and shift remaining cells up:
                r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
            }

        }

Posted 7-Sep-21 4:26am

Manidas Payyoli

Updated 7-Sep-21 4:28am

v2

Add a Solution

Comments

Richard MacCutchan 7-Sep-21 11:51am

for (int i = 2; i <= Rows; i++)

If there are only three rows then that will fail when i is equal to 3. The index values go from zero to rows-1.

[no name] 7-Sep-21 12:27pm

how would i change the loop .i have tried with 100 rows as well.for (int i = 0; i <= Rows-1; i++)

[no name] 7-Sep-21 12:29pm

This also not working

for (int i = 0; i <= Rows-1; i++)

{

var DepDel1 = (Excel.Range)xlWorksheetNew.Cells[i, 9];
string DepDel = DepDel1.Value2.ToString();

Excel.Range r = xlWorksheetNew.Range[xlWorksheetNew.Cells[i, 1], xlWorksheetNew.Cells[i, 10]];

if (DepDel == "01")
{
// if match, delete and shift remaining cells up:
r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
}

}

Richard MacCutchan 7-Sep-21 12:53pm

This would be a better loop.

for (int i = 0; i < Rows; i++) // the correct way to loop all rows
{
    var DepDel1 = (Excel.Range)xlWorksheetNew.Cells[i, 9];
    string DepDel = DepDel1.Value2.ToString();
    if (!string.IsNullOrEmpty(FepDel))
    {
        Excel.Range r = xlWorksheetNew.Range[xlWorksheetNew.Cells[i, 1], xlWorksheetNew.Cells[i, 10]];
        if (DepDel.Equals("01"))
        {
            // if match, delete and shift remaining cells up:
            r.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
        }
    }
}

However, you still have the issue that you are deleting rows from the list that you are iterating, which will cause problems. You need to identify the rows to delete, and make a note of them and then delete them later in reverse order.

1 solution

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)

Richard Deeming · Answer 1 · 2021-09-07T04:28:00

Solution 1

One of the variables in your code is null, and you are trying to access one of its properties or call one of its methods.

We can't tell you which, because we can't run your code, and we don't have access to your document.

Debug your code to find the variable which is null. Then work backwards to find out why it's null, and fix your code to avoid the problem.

Posted 7-Sep-21 4:28am

Richard Deeming

Comments

[no name] 7-Sep-21 10:56am

I am testing and debugging with only 3 rows.first row is heading.None of the value is null.first iteration it is inside the condition and next iteration row count is same as 3.it should 2 no?

Richard Deeming 7-Sep-21 10:57am

The error message you posted clearly says that one of the variables IS null.

You need to step through your code to find out which.

Richard Deeming 7-Sep-21 10:58am

NB: If you're walking through any list by index and deleting items from it, you should normally do that in reverse. Otherwise, you'll end up skipping elements in the list, since deleting an item will shift the index of all subsequent items up by one.

[no name] 7-Sep-21 11:28am

// excelRange.AutoFilter(17, "01", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true); can we have any method like this like i need to filter !="01"

[no name] 7-Sep-21 11:02am

yes i have deleteing logic just before this code like ((Excel.Range)xlWorksheetNew.Columns["J"]).Delete();
((Excel.Range)xlWorksheetNew.Columns["I"]).Delete();