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.
In this article, we will learn 3 ways to delete blank columns in Excel by selecting and deleting columns, using Excel editing tools, using a formula, and applying VBA code. We will use the following dataset to demonstrate the whole process. Here we have three blank columns (columns D, E, and G) in our working range that we need to delete.
1. Deleting Blank Columns in Excel After Selecting Manually Using Ctrl Key
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.
🔶 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 column headers and select Delete.
- As a result, we will see those blank columns are deleted.
Select the blank column/columns and then press Ctrl-. This will delete the blank columns.
Read more: How to Delete Unused Columns in Excel
2. Finding All Blank Columns Within Selection Using Excel Go To Special Command and Deleting 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.
- 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.
- Select Blanks in the Go To Special window and press OK.
We can 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 the left since there is no blank column in between.
Read more: How to Delete Empty Columns with Header in Excel
3. Removing Blank Columns After Finding Through COUNTA Function
You can use the COUNTA function to delete blank columns in Excel. The COUNTA function first identifies the blank and non-blank columns in your dataset in Excel, and then selects and deletes the blank columns.
🔶 Steps:
- 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.
=COUNTA(B5:B1048576)=0
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.
- 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.
- Select all the data and go to the Home tab in the ribbon.
- Select Sort & Filter > Custom Sort > Sort dialog box will appear.
- From the pop-up Sort window, click the Options.
- 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 and click OK.
- As a result, 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.
Read more: How to Delete Every Other Column in Excel
4. Applying 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:
- 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.
- In the window, select Insert > 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
- Next, 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. 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.
Download Practice Workbook
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 for solutions.
Related Articles
- How to Delete Multiple Columns in Excel
- How to Delete Column in Excel Without Affecting Formula
- How to Delete Multiple Columns in Excel
- How to Delete Column in Excel Without Affecting Formula
- How to Delete Unused Columns in Excel
- How to Delete Multiple Columns in Excel with Condition
- How to Delete Infinite Columns in Excel
- How to Delete Hidden Columns in Excel
- How to Delete Columns with Specific Text in Excel
- [Solved!] Can’t Delete Extra Columns in Excel
