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.
- Delete Blank Rows in Excel using C#
- Update References Automatically while Deleting Blank Rows
- Delete Blank Columns in Excel using C#
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#.
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.
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.
If we remove blank rows in Sheet1, the value email@example.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.
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.
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#.
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.