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.

4 Ways to Delete Blank Columns in Excel


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.

Selecting and Deleting Blank Columns in Excel

  • 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.

Read more: How to Delete Unused Columns in Excel


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.

Using Excel Editing Tools

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

Using Excel Editing Tools

  • 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.

Using Excel Editing Tools to delete blank columns in Excel


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

Using “COUNTA” Formula to Delete Blank Columns in Excel

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.

Using “COUNTA” Formula to Delete Blank Columns in Excel

  • 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.

Using Custom Sort to delete blank columns in Excel

  • 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.

Using “COUNTA” Formula to Delete Blank Columns in Excel

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

Read more: How to Delete Every Other Column in Excel


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

Applying VBA Code to Delete Blank Columns in Excel

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

  • Select the cell range after running the VBA.

Applying VBA Code

  • 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


Related Articles


<< Go Back to Delete Columns | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo