The tutorial will teach you how to remove empty columns in Excel with a macro, formula and a button-click.
As trivial as it sounds, deleting empty columns in Excel is not something that can be accomplished with a mere mouse click. It cannot be done in two clicks either. The prospect of reviewing all the columns in your worksheet and removing the empty ones manually is definitely something you'd want to avoid. Luckily, Microsoft Excel provides a great lot of different features, and by using those features in creative ways you can cope with almost any task!
Quick way to delete empty columns that you should never use
When it comes to removing blanks in Excel (whether it is empty cells, rows or columns), many online resources rely on the Go to Special > Blanks command. Never do that in your worksheets!
This method (F5 > Special… > Blanks) finds and selects all empty cells in the range:
If now you right-click the selected cells and choose Delete > Entire column, all the columns that contain at least one blank cell would be lost! If you've inadvertently did that, press Ctrl + Z to get everything back.
Now that you know a wrong way to delete blank columns in Excel, let's see how to do it right.
How to remove blank columns in Excel with VBA
Experienced Excel users know this rule of a thumb: not to waste hours doing something manually, invest a few minutes in writing a macro that will do it for you automatically.
The below VBA macro removes all blank columns in the selected range. And it does this safely - only absolutely empty columns are deleted. If a column contains a single cell value, even an empty string returned by some formula, such a column will remain intact.
Public Sub DeleteEmptyColumns() Dim SourceRange As Range Dim EntireColumn As Range On Error Resume Next Set SourceRange = Application.InputBox( _ "Select a range:", "Delete Empty Columns", _ Application.Selection.Address, Type:=8) If Not (SourceRange Is Nothing) Then Application.ScreenUpdating = False For i = SourceRange.Columns.Count To 1 Step -1 Set EntireColumn = SourceRange.Cells(1, i).EntireColumn If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then EntireColumn.Delete End If Next Application.ScreenUpdating = True End IfEnd Sub
How to use the Delete Empty Columns macro
Here are the steps to add the macro to your Excel:
- Press Alt + F11 to open the Visual Basic Editor.
- On the menu bar, click Insert > Module.
- Paste the above code in the Code window.
- Press F5 to run the macro.
- When the pop-up dialog appears, switch to the worksheet of interest, select the desired range, and click OK:
If you do not want to add a macro to your worksheet, you can run it from our sample workbook. Here's how:
- Download our sample workbook to Remove Blank Columns in Excel, open it, and enable content if prompted.
- Open your own workbook or switch to the already opened one.
- In your workbook, press Alt + F8, select the DeleteEmptyColumns macro, and click Run.
- In the pop-up dialog, select the range and click OK.
Either way, all empty columns in the selected range will be disposed of:
Identify and delete blank columns in Excel with a formula
The above macro removes empty columns quickly and silently. But if you are a "keep-everything-under-control" kind of person (like I am :) you may want to visually see the columns that are going to be removed. In this example, we will first identify blank columns by using a formula so that you could quickly review them, and then eliminate all or some of those columns.
Note. Before deleting anything permanently, especially by using an unknown technique, I strongly advise you to make a backup copy of your workbook, just to be on the safe side if something goes wrong.
With a backup copy in a safe place, perform the following steps:
Step 1. Insert a new row
Add a new row at the top of your table. For this, right-click the first row header and click Insert. Do not worry about mangling the structure/arrangement of your data - you can delete this row later.
Step 2. Identify empty columns
In the leftmost cell of the newly added row, enter the following formula:
=COUNTA(A2:A1048576)=0
And then, copy the formula to the other columns by dragging the fill handle.
The formula's logic is very simple: COUNTA checks the number of non-blanks cells in the column, from row 2 to row 1048576, which is a row maximum in Excel 2019 - 2007. You compare that number with zero and, as the result, have TRUE in blank columns and FALSE in the columns that contain at least one non-empty cell. Due to the use of relative cell references, the formula properly adjusts for each column where it is copied.
In case you are setting up the worksheet for someone else, you may want to label the columns in a more meaningful manner. No problem, this can be easily done with an IF statement similar to this:
=IF(COUNTA(A2:A1048576)=0, "Blank", "Not blank")
Now the formula explicitly indicates which columns are empty and which are not:
Tip. Compared to a macro, this method gives you more flexibility with regard to which columns should be considered blank. In this example, we check the whole table, including the header row. That means if a column contains only a header, such a column is not regarded as blank and is not deleted. If you'd like to check only data rows ignoring column headers, remove the header row(s) from the target range (A3:A1048576). As the result, a column that has a header and no other data in it will be deemed blank and subject to deleting. Also, you can limit the range to the last used row, which would be A11 in our case.
Step 3. Remove blank columns
Having a reasonable number of columns, you can simply select those that have "Blank" in the first row (to select multiple columns, hold the Ctrl key as you click the column letters). Then, right-click any selected column, and choose Delete from the context menu:
If there are tens or hundreds of columns in your worksheet, it makes sense to bring all empty ones to view. For this, do the following:
- Select the top row with formulas, go to the Data tab > Sort and Filter group, and click the Sort button.
- In the warning dialog box that appears, select Expand the selection, and click Sort…
- This will open the Sort dialog box, where you click the Options… button, select Sort left to right, and click OK.
- Configure just one sort level like shown below and click OK:
- Sort by: Row 1
- Sort On: Cell Values
- Order: A to Z
As the result, the blank columns will be moved to the left part of your worksheet:
- Select all blank columns - click on the first column letter, press Shift, and then click the letter of the last blank column.
- Right-click the selected columns and choose Delete from the pop-up menu.
Done! You've got rid of the blank columns, and there is nothing that would now prevent you from deleting the top row with the formulas.
Fastest way to remove empty columns in Excel
In the beginning of this tutorial, I wrote that there is no one-click way to delete blank columns in Excel. In fact, that isn't exactly true. I should have said there is no inbuilt way. The users of our Ultimate Suite can remove blanks in Excel literally in a couple of clicks :)
In the target worksheet, switch to the Ablebits Tools tab, click Delete Blanks and choose Empty Columns:
To make sure that wasn't an accidental mouse click, the add-in will ask you to confirm that you really want to remove empty columns from that worksheet:
Click OK, and in a moment all blank columns are gone!
Like the macro discussed above, this tool deletes only those columns that are absolutely empty. Columns that have any single value, including headers, are preserved.
Delete Blanks is just one of tens of wonderful features that can make your life as an Excel user easier. To find more, you are welcome to download a trial version of our Ultimate Suite for Excel.
Blank columns are not deleted! Why?
Issue: You have tried all of the above methods, but one or more empty columns are stuck in your worksheet. Why?
Most likely because those columns are not really empty. Many different characters invisible to the human eye may lurk unnoticed in your Excel spreadsheets, especially if you imported information from an external source. That can be a mere empty string or a space character, non-breaking space or some other non-printing character.
To pin down the culprit, select the first cell in the problematic column and press Ctrl + down arrow. For example, column C in the screenshot below is not blank because of a single space character in C6:
Double-click the cell to see what actually is in it or simply press the Delete key to get rid of the unknown something. And then repeat the above process to find out if there are any other invisible things in that column. You may also want to clean your data by removing leading, trailing and non-breaking spaces.
I thank you for reading and hope to see you on our blog next week!
You may also be interested in
- How to remove blank cells in Excel
- How to remove empty rows in Excel
- How to delete blank lines in Excel with VBA and formulas
- How to remove duplicates in Excel (duplicate rows, values and partial matches)
- How to delete every other row or every Nth row in Excel
