We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Delete range loses data validation

I recently reported a similar problem, but this one is new:

If I used cells.DeleteRange with ShiftType.Up, data validation that exist on cells below the range which is being deleted will not shift up. Values and formatting does, however.

See the attached designer document (DeleteRange2.xlsx) and the result (DeleteRange2_result.xlsx).

In the designer, cell D19 has data validation (List). After the range “DUP_Project” (C4:G5) has been deleted with ShiftUp, the D19 cell has been shifted up two rows. The value (“Please select a value”) and formatting is now in cell D17, but the data validation is left in D19.

The code below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Diagnostics;

namespace Infoweaver.Forms.AsposeTest
class DeleteRange
public enum DeleteRangeEnum
public static void DoIt()
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “DeleteRange2.xlsx”);
designer.Workbook = workbook;

Range range = workbook.Worksheets.GetRangeByName(“DUP_Project”);
Worksheet worksheet = range.Worksheet;

Cells cells = range.Worksheet.Cells;

DeleteRangeEnum mode = DeleteRangeEnum.ShiftCellsUp;

switch (mode)
case DeleteRangeEnum.ShiftCellsLeft:
case DeleteRangeEnum.ShiftCellsUp:
ShiftType shiftType = mode == DeleteRangeEnum.ShiftCellsLeft ? ShiftType.Left : ShiftType.Up;
cells.DeleteRange(range.FirstRow, range.FirstColumn, range.FirstRow + range.RowCount - 1, range.FirstColumn + range.ColumnCount - 1, shiftType);
case DeleteRangeEnum.DeleteEntireColumns:
cells.DeleteColumns(range.FirstColumn, range.ColumnCount, true);
case DeleteRangeEnum.DeleteEntireRows:
cells.DeleteRows(range.FirstRow, range.RowCount, true);
throw new InvalidOperationException(string.Format(“Unexpected DeleteRangeEnum: ‘{0}’.”, mode));
string output = Constants.destPath + “DeleteRange2_result.xlsx”;


I can find the issue as you mentioned in the output file after running your code with template file. I have logged a ticket with an id: CELLSNET-40133. We will look into it soon.

Thank you.


We have fixed this issue. Please download: Aspose.Cells for .NET v7.0.3.4

The issues you have found earlier (filed as CELLSNET-40133) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.