How to Delete Blank Columns in Excel (4 Quick Ways)

Here we have three blank columns (columns D, E, and G) in our working range that we need to delete.

Method 1 – Deleting Blank Columns in Excel After Selecting Manually Using the Ctrl Key

Steps:

Select the empty columns. If we have more than one column, press and hold Ctrl, then select them.

Right-click on one of these column headers and select Delete.

The blank columns are deleted.

Keyboard Shortcut to Delete Blank Columns:

Select the blank column/columns and then press Ctrl-. This will delete the blank columns.

Method 2 – Finding All Blank Columns Within Selection Using the Excel Go To Special Command and Deleting Them

Steps:

Select the dataset.

Go to the Find and Select option under the Editing section in the Home tab of the Ribbon.
Select Go To Special. A Go To Special dialog box will appear.

Select Blanks in the Go To Special window and press OK.

We can see all the blank boxes have been selected in that workspace.

Right-click on the selection and select Delete. A small dialog box will appear.

Select Shift cells left.

This will shift the columns to the left since there is no blank column in between.

Method 3 – Removing Blank Columns After Finding Them Through the COUNTA Function

Steps:

Insert a row on top of the dataset by selecting the row, right-clicking, and selecting Insert.

In the leftmost working cell of that blank row (in our case it’s B4), use the following formula and press Enter.

=COUNTA(B5:B1048576)=0

Here, B5 is the starting cell of our dataset. B1048576 is the maximum cell number possible in Excel (for Excel 365). The =0 checks the blanks, meaning, if there are only blank cells in the range.

Here’s the result.

Drag the Fill Handle to the right side to see the status of the rest of the columns.

The result will be like the image below.

Select all the data and go to the Home tab in the ribbon.
Select Sort & Filter, then Custom Sort. The Sort dialog box will appear.

From the pop-up Sort window, click Options.
Select Sort left to right from the Sort Options pop-up window and click OK.

Click the drop-down box under the Sort on label and select the option Cell Values from there.
Click the drop-down box under the Order label and select the option Largest to Smallest and click OK.

Clicking OK will give us results like the following.

Select the blank columns and delete them as mentioned in the first method.

You can also delete the True and False row if it’s no longer necessary.

Method 4 – Applying VBA Code to Delete Blank Columns

Steps:

Go to the Developer tab in the ribbon and click on Visual Basic.
A window named Microsoft Visual Basic for Application will appear. You can do the same by pressing Alt + F11.

In the window, select Insert, then Module.

In that window, copy and paste the following code.

Sub del_blank_col()
Dim SrcRng As Range
Dim FullCol As Range

On Error Resume Next

Set SrcRng = Application.InputBox("Source Range:", "Delete Blank Columns!", Application.Selection.Address, Type:=8)

If Not (SrcRng Is Nothing) Then
Application.ScreenUpdating = False

For i = SrcRng.Columns.Count To 1 Step -1
Set FullCol = SrcRng.Cells(1, i).FullCol
If Application.WorksheetFunction.CountA(FullCol) = 0 Then
FullCol.Delete
End If
Next

Application.ScreenUpdating = True
End If
End Sub

Run the VBA by selecting the Run button from the menu.

Select the cell range after running the VBA.

Pressing OK will immediately delete the blank rows.

Things to Remember

The select and delete feature is not suitable for large Excel files containing large amounts of data.
The editing tool is actually a special selection tool. So, we need to delete the blank columns separately after selection.
The COUNTA formula scans all the rows. For bigger files, it may take some time.
The VBA process is irreversible. We can’t undo the process. Keeping a backup is recommended.

Download the Practice Workbook

Delete Blank Columns.xlsm