I am trying to delete the first datarow of a ListObject via Cells.DataRange(). If i am deleting the first data row (after the header), the table breaks. If i save it the workbook to the file system, and try to add a new row to the table via excel, all the calculated fields appear as #REF. However the formula is correctly filled in.
If i am deleting anything else, the table works in excel as intended. The problem is only with the first row.
I am using the latest version of aspose cells .NET.
Please investigate this issue.
Best regards,
Andras
Summary
When the first data row of a ListObject (Excel table) is removed with Range.DeleteRange, the structured‑reference formulas in the table can turn into #REF! after the workbook is saved and opened in Excel. Deleting any other row does not show this behaviour.
Possible reason DeleteRange removes the cells but the table’s internal definition (the range stored in the table XML) may not be updated when the very first data row is removed. As a result the structured‑reference formulas still point to a row that no longer exists, which Excel resolves to #REF! on load.
Work‑around / mitigation
After deleting the first data row, refresh the table’s range:
// assume ws is the worksheet and table is the ListObject
var table = ws.ListObjects[0];
// delete the first data row (row index 1 inside the DataRange)
table.DataRange.DeleteRange(1, 0, 1, table.DataRange.ColumnCount, DeleteShiftDirection.LeftShift);
// rebuild a range that matches the new size of the table
int startRow = table.DataRange.FirstRow + 1; // first data row after deletion
int startCol = table.DataRange.FirstColumn;
int rowCount = table.DataRange.RowCount - 1; // exclude header
int colCount = table.DataRange.ColumnCount;
var newRange = ws.Cells.CreateRange(startRow, startCol, rowCount, colCount);
// update the table definition
table.Resize(newRange);
// optional: recalculate formulas
wb.CalculateFormula();
Save the workbook and verify that the calculated columns now show the expected values instead of #REF!.
Diagnostic checklist (useful if the issue persists)
Verify the table’s ref attribute in the generated xl/tables/tableX.xml matches the actual data range after deletion.
Check that the formula cells still contain the original structured reference (e.g., [@Qty]*[@Price]).
Ensure you are using the latest Aspose.Cells build; if the problem remains, collect a minimal reproducible workbook and the exact code snippet and submit them to Aspose support for deeper investigation.
Next steps
Apply the resize step shown above in your production code whenever you delete the first data row of a table. If you continue to see #REF! errors, provide the minimal workbook and code to Aspose support so the issue can be reproduced and addressed in a future release.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.
I am using Aspose.Cells 25.11.0
I am deleting the first row below the header. I am not trying to delete the header.
cells.DeleteRange(5, 2, 5, 8, ShiftType.Up);
@careall
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-59444
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@careall
Could you change formulas of table as Aspose_DeleteRange.zip (12.5 KB)?
It’s better that you can refer to table column name in calculated formula of the table.
If E6 is deleted , the formula “ROUND(IF($E6=0,0,$L$6/$H$3),0)” will be changed as “ROUND(IF(#REF!=0,0,$L$6/$H$3),0)” if it’s in a normal cell , not in table.
We have to check different delete operation behavior between cell and table.
I changed the formula as you suggested (same behavior). Thank you for the suggestion, technically it is a more robust solution, but unfortunately we are not always in control of the content of the used excel file, because in our product the customer can upload any excel file to use (has certain requirements of course). And we cannot make any restriction, since the customer is not doing anything wrong when writing the formula with an exact reference to a cell (like E6) instead of referencing the corresponding column.
Thank you for your feedback and for providing additional details.
We understand your situation and recognize that adjusting the formulas or contents of the Excel files is beyond your control. Rest assured that we have already logged the ticket (“CELLSNET-59444”) regarding the issue of inconsistent calculated column formulas after deleting the first data row of the table. We are working on resolving the issue soon. Once we have new updates on it, we will let you know here.
We are pleased to inform you that your issue (Ticket ID: “CELLSNET-59444”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.12) that we plan to release in the first half of December 2025. You will be notified when the next version is published.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.