Goal: Enable users to delete empty columns from selected columns in Excel.

This presentation will offer one way to delete empty columns from selected columns in Excel. We have some made up data from a QuickBooks report. The problem is the report doesn’t present the monthly income statement data in continuous columns.

Clearly it would be easier to reference this financial data. Where there are empty columns across the time series. Sure we can select each individual empty column and delete them accordingly. However, we want a more manageable solution via a customized macro, which will delete the empty columns of a selected range.

The delete empty columns macro

Go to Visual Basic for Applications and insert a Module. Then insert the “Option Explicit” syntax, we will be forced to state the variables for each new macro we compose. It helps to avoid errors occurring at compile time; in other words it helps to protect our macro from bugs.

Please notate your macro, as it will help future users to understand the purpose of the macro.

The macro will be called, loopColumnDeleteEmpty

Now it is prudent to disable these Excel properties, in order to enhance the macro’s performance. Next, we need to declare these specific object variables of the macro. Obviously the range object for our macro will be the Selection syntax.

The loop code is needed. It will cycle through all the cells in each column, across our selected columns. Thus, if all cells in a certain column are zero, it will delete that entire column.

This code, “Next cell”, will move the macro to the next column in the selection. Once the macro has run through all the columns, we will move the cursor to cell address “A1”.

The macro is complete.

We can re-enable the Excel properties, which were earlier disabled. It is crucial to re-enable these properties. Otherwise your application of Excel won’t automatically calculate, update the screen nor enable events such as automatically saving your files.

Before we run the macro, let’s review our code one final time

Running the macro

Let’s select some columns and run the macro. The macro is working

We will try it one final time, and select the entire time series of data. As we can see, the macro is correctly deleting all empty columns in our selected columns range.

Here is the example workbook which contains the delete empty columns macro.

Download Spreadsheet Vault SpreadsheetVault – Deleting empty columns in selection (solution).xlsb