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

In this article, we will learn about how you can delete blank columns in Excel. Blank columns can create problems while formula calculation or exporting data. Blank columns also increase the file size significantly. So, deleting blank columns is important in Excel.

Download Practice Workbook

You can download the practice workbook from here.

4 Ways to Delete Blank Columns in Excel

In this article, we will learn 3 ways to delete blank columns in Excel by selecting and deleting columns, using Excel editing tools, by using a formula, and by applying VBA code. We will use the following dataset to demonstrate the whole process. Here we have three blank columns (column D, E and G) in our working range that we need to delete.

1. Select Blank Columns with Ctrl Key and Delete

The simplest way to delete blank columns in excel. If there are a few blank columns that are in the working range, we can use this method. The steps are below.

🔶 Steps:

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

• Then Right-click on one of these columns’ heads and select Delete.

• As a result, we will see those blank columns are deleted.

Read more: How to Delete Unused Columns in Excel

2. Find All Blank Columns Within Selection Using Excel Go To Special Command and Delete Them

When we have a large number of columns that are not selectable by the method mentioned above, we will go for this method. The steps are described below.

🔶 Steps:

• First, select the dataset.

• Second, we will go to the Find and Select option under the Editing section in the Home tab of the Ribbon and select Go To Special. Go To Special dialog box will appear.

• Third, select “Blanks” in the Go To Special selection box and press OK.

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

• Then, right-click on the selection and select Delete. A small dialog box will appear.

• Now in the selection box, select Shift cells left.

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

Similar Readings

• How to Delete Multiple Columns in Excel
  
• How to Delete Extra Columns in Excel (7 Methods)
• Delete Columns in Excel Without Affecting Formula (Two Ways)

3. Use COUNTA Formula to Delete Blank Columns in Excel

You can utilize the COUNTA function to delete blank columns in Excel. What the COUNTA formula will do is, it will first identify the blank and non-blank columns in your dataset in Excel, and then, from there you can easily select and delete the blank columns. The steps are below.

🔶 Steps:

• First, insert a row on top of the dataset by selecting the row >> right-click on it >> select Insert.

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

Here, B5 is the starting cell of our dataset. B1048576 is the maximum cell number possible in Excel (applicable for Excel 365). The “=0” checks the blanks, meaning, if these cells in that range are all blanks or not. If blank or the condition is true, then it’ll show True in the cell, otherwise False.

• Next pressing Enter will give us this result.

• Now we will 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.

• Furthermore, select all the data and go to the Home tab in the Ribbon. Select Sort & Filter > Custom Sort. Sort dialog box will appear.

• Consequently, from the pop-up Sort window, click the Options Then select Sort left to right from the Sort Options pop-up window and click OK.

• Again, 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. Click OK.

• As a result, clicking OK will give us results like the following.

• Finally, select the blank columns and delete them as mentioned in the first method.

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

4. Apply a VBA Code to Delete Blank Columns

If you are an experienced Excel user, then this method is especially for you. Implementing VBA code is the safest way to delete blank columns in Excel. It deletes the absolutely empty columns only. If any column contains a single cell value, even if any cell returns an empty string, still that entire column will be completely intact. The steps of this method are below.

🔶 Steps:

• Firstly, 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 as well.

• Secondly, in the window, select Insert > Module.

• Thirdly, 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

• Next, run the VBA by selecting the Run button from the menu.

• Then select the cell range after running the VBA.

• Finally, 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.
• Editing tools is actually special selection tool. So, we need to delete the blank columns separately after selection.
• The COUNTA formula scans all the rows. So For bigger files, it may take some time.
• The VBA process is irreversible. We can’t undo the process. So keeping a backup is recommended.

Conclusion

These are all the methods to delete blank columns in Excel. Hope it’ll help you. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel-related problems, you can visit our website Exceldemy for solutions.

Further Readings

• How to Clear Contents in Excel Without Deleting Formulas (3 Ways)
• VBA to Delete Column in Excel (9 Criteria)
• VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)