Free Excel Add-in: Remove Empty Rows

We’re rolling out the Excel Virtuoso Store with the launch of a free product. An Excel Add-in to delete empty rows from datasets. This will be one of many free and premium Data Management tools provided through this site.

Overview

The Delete Empty Rows add-in will delete any empty rows or columns with datasets on the active spreadsheet.

Features

  • Detects the last row of usable data on the spreadsheet and will automatically set that as the range.
  • Allows you to select any column to test for blank rows.
  • Quickly deletes blank rows based on selected criteria.
  • Option allows rows to be deleted if either the entire row is blank, or a specific cell within each row is blank.
  • Comes with a “column mode”, providing the same features to manage blank columns.

Installation

Download the installer file from the store or by clicking the button below. You must go through the shopping cart, however, you will not be required to enter any credit card or payment information. A download link will be provided at the bottom of the Purchase Confirmation.

Once downloaded, unzip the zip file into a local directory — click on the Setup.exe file to install the add-in.

Depending on your Windows settings, you may receive some unknown publisher warnings during installation. This is expected — you can verify that the installer is from us if you see the “EV” logo on the upper right of the installer.

You may also receive a warning about add-ins the first time you open Excel after the install. This is standard for all add-in installs.

Instructions

We strongly suggest testing the add-in on dummy data, or use a work-in-progress copy to become familiar with how the add-in works. Keep in mind that actions completed by the Remove Empty Rows add-in cannot be undone with the undo function.

Remove Empty Rows

Here’s an example of a dataset with empty columns. This dataset lists the 36 largest cities in Europe.

2016-12-08-1

Navigate to the Delete Empty Rows tool by clicking on the (1) Data Management tab and selecting the (2) Remove Empty Rows add-in.


The Remove Empty Rows dialog will appear:

The add-in opens with default values ready to remove empty rows. Based on these defaults, the add-in will:

  1. Axis: Search rows for empty values.
  2. Set Range: Start the search on row 1, and end the search on row 42, which is the last used row as detected by the add-in. These values can be modified up to row 10,000.
  3. Control: Select if you want to detect if the entire row is empty, or a single cell within the row is empty. Upon selecting “Single Cell”, the “Search Column” picker will activate allowing you to adjust the search column.

After running, empty rows have been cleared from the dataset:

2016-12-08-3

Remove Empty Columns

The add-in can also be used to delete empty columns. The following dataset now has an empty column C.

2016-12-08-6

Navigate to the Delete Empty Rows add-in as instructed in the previous section.

The add-in opens with the Axis default option of “Delete Rows”. Click on the “Delete Columns” radio button, and defaults will change to detected column range.

  1. Axis: Search columns for empty values.
  2. Set Range: Start the search on column 1 (A), and end the search on column 5 (E), which is the last used column as detected by the add-in. These values can be modified up to column 10,000.
  3. Control: Select if you want to detect if the entire column is empty, or a single cell within the column is empty. Upon selecting “Single Cell”, the “Search Row” picker will activate allowing you to adjust the search row.

Other Notes

  • The “Single Cell” Control feature searches for a single empty cell as specified in the Control section. If values exist to the right or left of that column (for row deletions), or above or below of that row (for column deletions), the entire row/column will still be deleted. Use of the “Entire Row” (default) option is suggested to avoid this issue.
  • Download Page

Version History

Version 1.10
  • Added MSI installer.
  • Addressed form alignment/size issues.
  • Added functionality to search enter row/column range.
Version 1.05
  • First release.

Leave a Reply