Deleting duplicate rows in Excel using Epplus

Question

I have a worksheet with a number of rows and several columns. I want to delete all duplicate rows in this worksheet. In other words, the highlighted rows in this screenshot should be deleted, and the rows below should be moved up:

and should result in the following:

I'm using the following snippet of code:

List<int> rowsToDelete = new List<int>();
for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
{
    string a = worksheet.Cells[row,1].Value.ToString();
    string b = worksheet.Cells[row,2].Value.ToString();
    string c = worksheet.Cells[row,3].Value.ToString();

    int i = row + 1;
    while (worksheet.Cells[i,1].Value.ToString().Equals(a) &&
           worksheet.Cells[i,2].Value.ToString().Equals(b) &&
           worksheet.Cells[i,3].Value.ToString().Equals(c))
    {
        rowsToDelete.add(i);
        i++;
    }
}
foreach (var row in rowsToDelete)
{
    worksheet.Delete(row);
}

It is not deleting the correct rows. How can I fix this?

This is using Epplus 4.5.3.3 and .NET Framework 4.6.1

Answer 1

1. You need to re-think this… the while loop… as soon as it finds a row that is NOT a duplicate then it will exit the while loop. ...
2. @JohnG that is the purpose of the while loop, it should iterate through the duplicates only for a particular row.