Filter Blank and Non-Blank Cells in Excel using C#

Blank cells can cause problems in Excel reports and analyses. Filtering them helps clean and organize data for better results. Automating how to filter blank cells in Excel can save time and improve productivity. With just a few lines of code, you can handle big spreadsheets quickly and accurately. This blog will guide you through the process of filtering blank and non-blank cells using C#. Let’s dive in!

This article covers the following topics:

C# Excel Library to Filter Blank and Non-Blank Cells

Aspose.Cells for .NET is a powerful library that helps developers create, edit, and manage Excel files without using Microsoft Excel. It gives you full control over spreadsheets, including the ability to apply filters to data.

When it comes to filtering blank or non-blank cells, Aspose.Cells makes the process simple. You can add a filter to a specific column, choose blank or non-blank options, and update the sheet — all through clean and easy-to-read code. This saves time and reduces errors compared to manual editing.

Whether you’re building reports, cleaning data, or preparing summaries, Aspose.Cells lets you automate the filtering process efficiently.

Before you start, make sure you have the following:

Installing Aspose.Cells for .NET

  1. Install via NuGet Package Manager:

    • Open your .NET project in Visual Studio.
    • Go to Tools → NuGet Package Manager → Manage NuGet Packages for Solution.
    • Search for Aspose.Cells and install the package.

    Or run this command in the Package Manager Console:

    Install-Package Aspose.Cells

  2. Download manually:

How to Filter Blank Cells in C# Using Aspose.Cells

Filtering blank cells in Excel with Aspose.Cells is a simple process. You load the file, apply an AutoFilter to a specific range, and filter out the blank entries. Here’s how you can do it step-by-step:

1. Loading an Excel File

First, you need to load your Excel file into a Workbook object.

// Load an existing Excel file
Workbook workbook = new Workbook("input.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

2. Applying an AutoFilter

Set the range where you want to apply the filter. Usually, this is the table or data area you want to clean.

// Apply AutoFilter on the desired range
worksheet.AutoFilter.Range = "A1:C20"; // Adjust the range based on your data

3. Filtering for Blank Cells

Now, add a filter to a specific column. Here we will filter blank cells in the first column (index 0).

Method 1: Call MatchBlanks function to apply the filter

worksheet.AutoFilter.MatchBlanks(1);

Method 2: Call AddFilter function and set criteria to null

worksheet.AutoFilter.AddFilter(1, null);

Method 3: Apply filter for blank cells in the first column

worksheet.AutoFilter.AddFilter(0, ""); // Empty string represents blank cells
worksheet.AutoFilter.Refresh();

Finally, save the filtered file.

// Save the updated file
workbook.Save("filtered_blank_cells.xlsx");

Complete Code Example: Filtering Blank Cells in C#

How to Filter Blank Cells in C# Using Aspose.Cells

How to Filter Blank Cells in C# Using Aspose.Cells

Understanding the Key Classes Used in Filtering

  • AutoFilter.Range
    Defines the cell range where the filter will be applied.

  • AddFilter(columnIndex, criteria)
    Adds a filter condition to a specific column. In this case, an empty string "" filters blank cells.

  • Refresh()
    Applies and updates the filter based on the added criteria.

Filter Non-Blank Cells in Excel using C#

Filtering non-blank cells using Aspose.Cells is just as easy. Instead of setting the filter to an empty string, you can filter by all available values except blanks.

// Filter out blank cells in the first column (index 0)
worksheet.AutoFilter.Filter(0, 0); // 0 represents filtering non-blank cells

Small Modification to Filter Only Non-Empty Cells

You can use the MatchNonBlanks() method directly. Alternatively, you can manually list known non-blank entries if needed.

Filter Non-Blank Cells in Excel using C#

Filter Non-Blank Cells in Excel using C#

If you want more control (like filtering by specific text values), you can use AddFilter multiple times with actual values instead of an empty string.

Combining Multiple Filters in C#

Aspose.Cells for .NET also lets you apply multiple filters at once. You can filter by blank cells, specific text values, or numbers — all within the same range.

This is useful when you want to:

  • Show only blank cells and certain specific entries.
  • Combine multiple conditions in one column.
  • Apply different filters across multiple columns.

Example: Combining Filters

Combining Multiple Filters in C#

Combining Multiple Filters in C#

In this example:

  • It shows both rows with blank cells and rows where the country code column is “AFG”.
  • You can add as many filter values as needed before calling Refresh().

Tip: To filter on multiple columns, simply use AddFilter on other column indexes.

Get a Free License

Are you interested in exploring Aspose products? Visit the license page to obtain a free temporary license. It’s easy to get started, and you can experience the full capabilities of Aspose.Cells for .NET!

Excel Filter Blanks: Free Resources

Want to explore more about working with Excel files using Aspose.Cells for .NET? Check out these free resources:

Conclusion

Filtering blank or non-blank cells in Excel becomes simple and efficient with Aspose.Cells for .NET. You can automate data cleanup, build smarter reports, and save hours of manual work. Whether you’re working with small files or massive datasets, Aspose.Cells gives you full control over filtering, formatting, and exporting Excel files through code.

If you have any questions or need further assistance, please feel free to reach out at our free support forum.

See Also