Delete Blank Columns in Excel (3 Ways)

This article will show you how you can delete blank columns in Excel in three effective and easy ways.

Download Workbook

You can download the practice workbook from here.

3 Simple Ways to Delete Blank Columns in Excel

Here you will learn 3 ways to delete blank columns in Excel by using Excel editing tools, by using a formula, and by implementing VBA code.

1. By Using Excel Editing Tools

Step 1: Select the dataset.

Step 2: Go to Home -> Find & Select -> Go To Special.

delete blank columns in excel with find & select option

Step 3: From the Go To Special pop-up window, select Blanks, then click OK.

selecting blanks option to delete blank columns

Step 4: Only the empty cells from your dataset will be selected. Right-click the mouse, from the pop-up option list, select Delete.

deleting blank columns from option

Step 5: Next, from the pop-up Delete box, pick Shift cells left from the options. Click OK.

shifting cells left to delete blank columns

This will delete all the blank columns from the dataset.

dataset without blank columns

Read more: How to Delete Unused Columns in Excel

2. By Using Formula

You can utilize COUNTA() formula to delete blank columns in Excel. What 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.

This is another efficient way to delete blank columns in Excel, especially when you have a lot of blank columns to work with. Let’s learn how to do that.

Step 1: Insert an empty row right above the dataset by right-clicking the first row header and selecting Insert from the pop-up options list. This will insert a new row above the whole dataset.

inserting empty row above dataset

Step 2: In the leftmost cell of your worksheet, write the following formula,

=COUNTA(A2:A1048576)=0

Here,

A2 represents the first cell of the dataset,

A1048576 represents the row maximum in Excel (2007-2019).

Step 3: Press Enter. It will display True if the column is empty, or False if there is at least a single non-empty cell in the entire column.

Step 4: Apply the formula to the other columns by dragging the Fill Handle.

COUNTA() formula to identify blank columns

Step 5: Now go to Home -> Sort & Filter -> Custom Sort.

selecting sort and filter to identify blank columns

Step 6: From the pop-up Sort window, click the Options button. Then select Sort left to right from the Sort Options pop-up window and click OK.

selecting options for columns

Step 7: Click the drop-down box beside the Sort by label and from there select the option Row 1.

sorting by first row

Step 8: Click the drop-down box under the Sort on label and select the option Cell Values from there.

sorting based on cell values

Step 9: Click the drop-down box under the Order label and select the option Largest to Smallest. Click OK.

selecting the order of the columns

 It will shift all the blank columns to the left side of the worksheet.

all the blank columns are in the left

Step 10: From there just select the whole dataset of empty blank columns, right-click and then select Delete.

deleting the empty columns

Step 11: From the pop-up Delete box, pick the Entire column from the options. Click OK.

deleting the entire columns

This will delete all the blank columns from the dataset.


Similar Readings:


3. By Using VBA Code

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 any cell returns an empty string, still that entire column will be completely intact.

Below is the automated way of deleting blank columns in Excel.

Step 1: Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open the Visual Basic Editor.

opening visual basic

Step 2: From the menu bar, click Insert -> Module.

inserting module in VBA code window

Step 3: Copy the following code and paste it into the code window.

Public Sub DeleteBlankColumns()
    Dim SrcRange As Range
    Dim EntrColumn As Range
    On Error Resume Next
    Set SrcRange = Application.InputBox( _
        "Select a range:", "Delete Blank Columns", _
        Application.Selection.Address, Type:=8)
    If Not (SrcRange Is Nothing) Then
        Application.ScreenUpdating = False
        For i = SrcRange.Columns.Count To 1 Step -1
            Set EntrColumn = SrcRange.Cells(1, i).EntrColumn
            If Application.WorksheetFunction.CountA(EntrColumn) = 0 Then
                EntrColumn.Delete
            End If
        Next
        Application.ScreenUpdating = True
    End If
End Sub

Step 4: Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

running the macro vba code

Step 5: From the pop-up dialogue box, switch to the worksheet of interest, select the desired range and click OK.

selecting the data range in vba

This will delete all the blank columns from the dataset.

Conclusion

This article discussed three easy ways of how to delete blank columns in Excel. We have shown how to use Excel editing tools to delete blank columns, we have shown how to apply formulas to delete blank columns and we have also implemented the VBA code to delete blank columns in Excel.


Further Readings

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo