How to Trim Cell Range

by Chaknith Bin

The IronXL library allow to removes all empty rows and columns on the range borders in C# code without using Office Interop. This feature allows for efficient data processing and manipulation without the overhead of interacting with the Office suite.

C# NuGet Library for Excel

Install with NuGet

Install-Package IronXL.Excel
C# Excel DLL

Download DLL

Download DLL

Manually install into your project

Trim Cell Range Example

Select the desired Range of cells and apply Trim method on it. This method trims leading and trailing empty cells from the selected range.

The Trim method does not remove empty cells located in the middle of rows and columns within the range. To address this, you can apply sorting to push those empty cells to either top or bottom of the range.

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet workSheet = workBook.DefaultWorkSheet;

workSheet["A2"].Value = "A2";
workSheet["A3"].Value = "A3";

workSheet["B1"].Value = "B1";
workSheet["B2"].Value = "B2";
workSheet["B3"].Value = "B3";
workSheet["B4"].Value = "B4";

// Retrieve column
RangeColumn column = workSheet.GetColumn(0);

// Apply trimming
Range trimmedColumn = workSheet.GetColumn(0).Trim();
Imports IronXL

Private workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

Private workSheet("A2").Value = "A2"
Private workSheet("A3").Value = "A3"

Private workSheet("B1").Value = "B1"
Private workSheet("B2").Value = "B2"
Private workSheet("B3").Value = "B3"
Private workSheet("B4").Value = "B4"

' Retrieve column
Private column As RangeColumn = workSheet.GetColumn(0)

' Apply trimming
Private trimmedColumn As Range = workSheet.GetColumn(0).Trim()
VB   C#
Trim Column

Chaknith Bin

Software Engineer

Chaknith is the Sherlock Holmes of developers. It first occurred to him he might have a future in software engineering, when he was doing code challenges for fun. His focus is on IronXL and IronBarcode, but he takes pride in helping customers with every product. Chaknith leverages his knowledge from talking directly with customers, to help further improve the products themselves. His anecdotal feedback goes beyond Jira tickets and supports product development, documentation and marketing, to improve customer’s overall experience.When he isn’t in the office, he can be found learning about machine learning, coding and hiking.