Delete Range of Cells

Aspose.Cells Product Family
1

Is it possible to delete a range of cells from the template Excel sheet? If so, how are the cells shifted (left, up)?

Thanks!

2

In the latest hofix, you can use the following approaches to delete a range of cells.

1.Use Cells.DeleteColumn 3 method to delete cells in a column. Other cells on the right will be shifted left.

  1. Use Cells.DeleteRow 5method to delete cells in a row. Other cells on the bottom will be shifted up.

  2. Use Cells.ClearContents 6 to clear contents of a range. No shift to other cells.

  3. Use Cell.ClearRange 9to clear contents and formatting of a range. No shift to other cells.

3

Is there an equivalent to selecting a range of cells in Excel and deleting it, having the cells either to the right or below shift up or left, respectively? Deleting an entire column is not really an option and neither is just clearing the cells.

Thanks for the prompt reply,
Fluid Media

4

Now there isn’t such a method. We will add in the next release.

5

Hi Fluid,

Now it’s available. Please download the latest hotfix and try Cells.DeleteRange method.

6

@FluidMedia,
Please note that Aspose.Excel is discontinued and is replaced by an advanced version Aspose.Cells. This new product is better than the predecessor in terms of performance and range of supported features/functions. We can create and delete ranges using Aspose.Cells too as demonstrated in the following sample code:

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets[0];

// Create a range of cells based on H1:J4.
Range range = worksheet1.Cells.CreateRange("A1", "C4");

// Name the range.
range.Name = "MyRange";

// Input some data into cells in the range.
range[0, 0].PutValue("USA");
range[0, 1].PutValue("SA");
range[0, 2].PutValue("Turkey");
range[1, 0].PutValue("UK");
range[1, 1].PutValue("AUS");
range[1, 2].PutValue("Canada");
range[2, 0].PutValue("France");
range[2, 1].PutValue("Pakistan");
range[2, 2].PutValue("Egypt");
range[3, 0].PutValue("China");
range[3, 1].PutValue("Philipine");
range[3, 2].PutValue("Brazil");

worksheet1.Cells["D1"].Value = "Save Data 1";
worksheet1.Cells["D2"].Value = "Save Data 2";
worksheet1.Cells["D3"].Value = "Save Data 3";
worksheet1.Cells["D4"].Value = "Save Data 4";
workbook.Save("BeforeDeletingRange.xlsx");
// Getting the specified named range
Range range2 = workbook.Worksheets.GetRangeByName("MyRange");

workbook.Worksheets[0].Cells.DeleteRange(
    range.FirstRow,
    range.FirstColumn,
    range.FirstRow + range.RowCount-1,
    range.FirstColumn+ range.ColumnCount-1, ShiftType.Left);
workbook.Save("AfterDeletingRange.xlsx");

For more information about the named ranges follow the link below:
Create Access and Copy Named Ranges

You may download the free trial version from the following link:
Aspose.Cells for .NET(Latest version)

You may download a runnable solution here for testing different functions of this new product.