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.
- First, select the empty columns. If we have more than one column, then press Ctrl and then select them.
- As a result, we will see those 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
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.
- 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.
- 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.
- 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.
- 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.
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.