Click here to Skip to main content
15,064,665 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Updated 7-Sep-21 4:28am
v2
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.
Manidas Payyoli 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++)
Manidas Payyoli 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

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.
   
Comments
Manidas Payyoli 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.
Manidas Payyoli 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"
Manidas Payyoli 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();

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