How to delete empty columns in Excel

Need to delete empty columns in Excel on one sheet or on several? Unlike rows, Excel has no filter for columns, so the usual tricks don’t work. Here are a few simple ways to get rid of them.

Find empty columns with a formula

In a free row above or below the table, put this formula under the first column and fill it to the right. It returns TRUE for columns that are completely empty within the data range:

=COUNTA(A2:A1000)=0

Then select the columns marked TRUE (hold Ctrl and click their letters), right-click and choose Delete. Fine for a dozen columns, tedious for a hundred.

In Excel 365, a copy of the table without empty columns is one formula:

=FILTER(A1:Z1000,BYCOL(A1:Z1000,LAMBDA(c,COUNTA(c)>0)))

VBA code

You can delete empty columns with simple VBA code. The first procedure removes empty columns from a given sheet, the second runs it on the active sheet, and the third on all sheets of the workbook. It goes from right to left, so that deleting a column doesn’t shift the ones still to be checked:

Sub DeleteEmptyColumnsOnSheet(ws As Worksheet)
    Dim i As Long, lastCol As Long
    lastCol = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1
    For i = lastCol To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Columns(i)) = 0 Then ws.Columns(i).Delete
    Next i
End Sub

Sub DeleteEmptyColumnsActiveSheet()
    DeleteEmptyColumnsOnSheet ActiveSheet
End Sub

Sub DeleteEmptyColumnsInWorkbook()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        DeleteEmptyColumnsOnSheet ws
    Next ws
End Sub

How to use the code

• Press Alt+F11 to open the VBA editor.
• Right-click your workbook in the project tree and choose Insert → Module.
• Paste the code into the empty module window.
• Run DeleteEmptyColumnsActiveSheet or DeleteEmptyColumnsInWorkbook with Alt+F8.

There are a couple of catches:

• the code can run only while the workbook where it’s saved is open, which is very inconvenient;
• regular .xlsx files can’t store code, so you have to save the workbook as .xlsm or .xlsb;
• a macro can’t be undone with Ctrl+Z, so save a copy first.

Storing the code in the Personal Macro Workbook (PERSONAL.XLSB) is a bit more convenient: then it’s available in every workbook.

Delete empty columns in 2 clicks with !SEMTools

The solutions above work, but once you get into automation, the number of formulas and macros grows fast, and so does the time spent looking for the right one. My add-in solves this: hundreds of small solutions are arranged as intuitively as possible.

Delete empty columns on a sheet

With !SEMTools, deleting the empty columns from the active sheet is very simple: choose the option in the DELETE → Ranges → Columns menu, and all empty columns are deleted instantly.

Delete empty columns in a workbook

If there are empty columns on several sheets, !SEMTools deletes them from all sheets of the workbook at once.

The procedure is available in the free version of !SEMTools. Install it and use it.

Summary

I hope you found what you were looking for on this page and were able to delete the empty columns. A COUNTA check row works for small tables, FILTER with BYCOL in Excel 365 gives a clean copy, VBA handles whole workbooks, and the free !SEMTools procedure does the same in 2 clicks without any code.

You may also be interested in how to delete empty rows in Excel.