Sometimes, you may find it difficult to read or edit data in an Excel worksheet because it contains blank cells, rows or columns. It is possible to delete the blank cells, rows and columns manually, however, if the file contains a large amount of data, you may waste a lot of time doing so. In this article, I will demonstrate how to delete blank cells, rows and columns in Excel programmatically using C# and VB.NET.
Installation
In order to work with Excel, I will be using Free Spire.XLS for .NET, which is a free and easy-to-use API for creating, editing, converting and printing Excel files.
The DLL files of Free Spire.XLS for .NET can be either downloaded from the official website or installed via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then add the following code:
PM> Install-Package FreeSpire.XLS
Delete Blank Cells in Excel in C# and VB.NET
The following are the steps to delete blank cells in Excel:
- Create an instance of Workbook class.
- Load the Excel file using Workbook.LoadFromFile() method.
- Get the worksheet that contains blank cells using Workbook.Worksheets[sheetIndex] property.
- Get the row count and column count using Worksheet.Rows.Count() and Worksheet.Columns.Count() methods.
- Loop through the rows and columns, access the current cell using Worksheet.Range[rowIndex, columnIndex] property. Note the row index and column index here are 1-based.
- Determine if the current cell is blank or not using XlsRange.IsBlank Property. If any blank cell is found, remove it using XlsWorksheet.DeleteRange(CellRange, DeleteOption) method.
- Save the result file using Workbook.SaveToFile() method.
C#
using Spire.Xls;
using System.Linq;
namespace DeleteBlankCells
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Sample1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the row count
int maxRow = sheet.Rows.Count();
//Get the column count
int maxColumn = sheet.Columns.Count();
//Loop through the rows and columns
for (int i = maxRow - 1; i >= 0; i--)
{
for (int j = maxColumn - 1; j >= 0; j--)
{
//Get the current cell
CellRange cell = sheet.Range[i + 1, j + 1];
//Determine if the cell is blank or not
if (cell.IsBlank)
{
//Remove the blank cell with DeletOption (MoveLeft/MoveUp)
sheet.DeleteRange(cell, DeleteOption.MoveLeft);
}
}
}
//Save the result file
workbook.SaveToFile("DeleteBlankCells.xlsx", ExcelVersion.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Namespace DeleteBlankCells
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'Instantiate a Workbook object
Dim workbook As Workbook = New Workbook()
'Load the Excel file
workbook.LoadFromFile("Sample1.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get the row count
Dim maxRow As Integer = sheet.Rows.Count()
'Get the column count
Dim maxColumn As Integer = sheet.Columns.Count()
'Loop through the rows and columns
For i = maxRow - 1 To 0 Step -1
For j = maxColumn - 1 To 0 Step -1
'Get the current cell
Dim cell As CellRange = sheet.Range(i + 1, j + 1)
'Determine if the cell is blank or not
If cell.IsBlank Then
'Remove the blank cell with DeletOption (MoveLeft/MoveUp)
sheet.DeleteRange(cell, DeleteOption.MoveLeft)
End If
Next
Next
'Save the result file
workbook.SaveToFile("DeleteBlankCells.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
Delete Blank Rows in Excel in C# and VB.NET
The following are the steps to delete blank rows in Excel:
- Create an instance of Workbook class.
- Load the Excel file using Workbook.LoadFromFile() method.
- Get the worksheet that contains blank rows using Workbook.Worksheets[sheetIndex] property.
- Get the row count using Worksheet.Rows.Count() method.
- Loop through the rows in the worksheet.
- Determine whether the current row is blank or not using Worksheet.Rows[rowIndex].IsBlank property. If any blank row is found, remove it using XlsWorksheet.DeleteRow(rowIndex) method (note that the row index in the DeleteRow method starts from 1).
- Save the result file using Workbook.SaveToFile() method.
C#
using Spire.Xls;
using System.Linq;
namespace DeleteBlankRows
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Sample2.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the row count
int rowCount = sheet.Rows.Count();
//Loop through the rows
for (int i = rowCount - 1; i >= 0; i--)
{
//Determine if the row is blank or not
if (sheet.Rows[i].IsBlank)
{
//Delete blank row
sheet.DeleteRow(i + 1); //Index parameter in DeleteRow method starts from 1
}
}
//Save the result file
workbook.SaveToFile("DeleteBlankRows.xlsx", ExcelVersion.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Namespace DeleteBlankRows
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'Instantiate a Workbook object
Dim workbook As Workbook = New Workbook()
'Load the Excel file
workbook.LoadFromFile("Sample2.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get the row count
Dim rowCount As Integer = sheet.Rows.Count()
'Loop through the rows
For i = rowCount - 1 To 0 Step -1
'Determine if the row is blank or not
If sheet.Rows(i).IsBlank Then
'Delete blank row
sheet.DeleteRow(i + 1) 'Index parameter in DeleteRow method starts from 1
End If
Next
'Save the result file
workbook.SaveToFile("DeleteBlankRows.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
Delete Blank Columns in Excel in C# and VB.NET
The steps to delete blank columns is very similar to the steps of deleting blank rows, so I’m going to omit it here.
C#
using Spire.Xls;
using System.Linq;
namespace DeleteBlankRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Sample3.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the column count
int columnCount = sheet.Columns.Count();
//Loop through the columns
for (int j = columnCount - 1; j >= 0; j--)
{
//Determine if the column is blank or not
if (sheet.Columns[j].IsBlank)
{
//Delete blank column
sheet.DeleteColumn(j + 1); //Index parameter in DeleteColumn method starts from 1
}
}
//Save the result file
workbook.SaveToFile("DeleteBlankColumns.xlsx", ExcelVersion.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Namespace DeleteBlankRowsAndColumns
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'Instantiate a Workbook object
Dim workbook As Workbook = New Workbook()
'Load the Excel file
workbook.LoadFromFile("Sample3.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get the column count
Dim columnCount As Integer = sheet.Columns.Count()
'Loop through the columns
For j = columnCount - 1 To 0 Step -1
'Determine if the column is blank or not
If sheet.Columns(j).IsBlank Then
'Delete blank column
sheet.DeleteColumn(j + 1) 'Index parameter in DeleteColumn method starts from 1
End If
Next
'Save the result file
workbook.SaveToFile("DeleteBlankColumns.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
See More
Product Page | Documentation | Examples | Forum |
Office Dev Blog 