Read and complete the full issue template

Do you want to request a feature or report a bug?

• Bug
• Feature

If you are logging a possible bug or feature request, please test with the latest development build first.

Version of ClosedXML

0.93

What is the current behavior?

Say, we have a named range referring to the range A1:B2. If rows from 1 to 5 are deleted the named range starts to refer to A1:B1. If, instead, first two columns are deleted the named range becomes A1:A2.

What is the expected behavior or new feature?

The named range should become invalid: e.g. Sheet1!#REF!.

Did this work in previous versions of our tool? Which versions?

No

Notes

This issue should be addressed together with #686.

Also, I noticed that when multiple rows are deleted (e.g. ws.Rows(1, 100).Delete();) the formula shifting is performed for every single row. This may be improved by doing a single shift for the specified number of rows. Same is true for columns.

Reproducibility

Code to reproduce problem:

[Test]
public void NamedRangeBecomesInvalidOnRangeDeleting()
{
    using (var wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add("Sheet 1");
        ws.Range("A1:B2").AddToNamed("Simple", XLScope.Workbook);
        wb.NamedRanges.Add("Compound", new XLRanges
        {
            ws.Range("C1:D2"),
            ws.Range("A10:D15")
        });

        ws.Rows(1,5).Delete();

        Assert.AreEqual(2, wb.NamedRanges.Count());
        Assert.AreEqual(0, wb.NamedRanges.ValidNamedRanges().Count());
        Assert.AreEqual("'Sheet 1'!#REF!", wb.NamedRanges.ElementAt(0).RefersTo);
        Assert.AreEqual("'Sheet 1'!#REF!,'Sheet 1'!A5:D10", wb.NamedRanges.ElementAt(0).RefersTo);
    }
}

• I attached a sample spreadsheet. (You can drag files on to this issue)