Delete Blank Rows and Columns in Excel using C#

In this article, I will explain how to delete blank rows and columns in an excel file using C#. I will also explain how to update references automatically (used in formulas, charts and tables) while deleting blank rows and columns.

C# API to Remove Blank Rows and Columns

Aspose.Cells for .NET is a well-known spreadsheet manipulation API that lets you create and process Excel files from within your .NET applications. The API allows you to remove blank rows and columns in the Excel files in a few lines of code. You can either download the binaries of the API or get it installed using NuGet.

PM> Install-Package Aspose.Cells

Delete Blank Rows in Excel using C#

The following are the steps to delete all blank rows in Excel using C#.

  • Open an Excel file using the Workbook object.
  • Access the worksheet that has the blank rows. The worksheet can be accessed either by index (zero-based) or by name.
  • Call Cells.DeleteBlankRows() method to delete all blank rows that do not contain any data.

The following sample code shows how to remove blanks rows in Excel using C#.

Delete Blank Rows
Fig 1: Delete Blank Rows

Please note that Cells.DeleteBlankRows method removes the blank rows even if some sort of formatting is applied to them. It also removes the formatted blank rows below your data.

Delete Formatted Blank Rows
Fig 2: Delete Formatted Blank Rows

If you want to delete blank rows from all worksheets in an Excel document, simply iterate over WorksheetCollection and call DeleteBlankRows method on each worksheet as shown in the following code:

Update References Automatically while Deleting Blank Rows

Deleting blank rows can break references in formulas, charts and tables. For example, the cell B2 in the second worksheet has a formula =Sheet1!C3 which is referring to cell C3 in the first worksheet as shown in the following figure.

A cell in Sheet2 is referring to a value in Sheet1.
Fig 3: A cell in Sheet2 is referring to a value in Sheet1.

If we remove blank rows in Sheet1, the value lima@gmail.com will move to cell C1. But the formula (=Sheet1!C3) will not update and the cell B2 will contain an invalid value as shown below.

After removing blank rows, formula in Cell B2 has not updated.
Fig 4: After removing blank rows, formula in Cell B2 has not updated.

We can avoid this issue by using DeleteOptions.UpdateReference property and set it to true. It will ensure that the references are updated while deleting blank rows. The following sample code shows how to use DeleteOptions.UpdateReference property.

As shown in the following image, the formula has been updated and the cell B2 contains a valid value.

Formula has been updated and the cell contains a valid value.
Fig 5: Formula has been updated and the cell contains a valid value.

Delete Blank Columns in Excel using C#

The steps to delete blank columns are same as for deleting blank rows. We use Cells.DeleteBlankColumns method to delete all blank columns that do not contain any data. The following sample code shows how to delete blank rows and columns in C#.

Delete Blank Rows and Columns
Fig 6: Delete Blank Rows and Columns

Conclusion

In this article, you have learnt how to delete blank rows and columns in Excel file using C#. Moreover, you have learnt how to update references (used in formulas, charts and tables) automatically while deleting blank rows and columns. Please check the documentation of Aspose.Cells for .NET for more information. If you have any questions, please feel free to post them at our Support Forum. We will answer them in a few hours.

See Also