Maruf Islam
Deleting multiple columns; empty or unnecessary is quite a desired task in Excel operations. In this article, we use Excel tools such as Delete, Cell Option, Function & VBA macro code to delete consecutive or non-consecutive empty or unnecessary columns.
We generate a dataset of employees’ Ids, Names, Origins, etc; where some columns are kept emptied & some are unnecessary.
Dataset for Download
5 Easy Ways to Delete Multiple Columns in Excel
Method 1:Using Right Click Delete Option
Suppose, in my dataset Origin, Joining Month & Joining Date are unnecessary. I have to delete them.
i) Deleting Consecutive Multiple Columns
Step 1: Select the three columns (Origin, Joining Month & Joining Date).Right-click on any selected column. An options menu will pop up.
You can Press CTRL & click on the columns you want to select.Â
Another way to do this is, just to put the cursor on any column indicator then press the Left Mouse key until it turns
and drag the cursor over the columns.
Step 2: Click Delete. The outcome will be similar to the one shown in the picture below
ii) Deleting Random Empty Multiple Empty ColumnsÂ
Step 1: Select random multiple empty columns pressing CTRL then click on the columns.
Step 2: Right Click on the selected columns, a window will pop up then Click Delete. The outcome is shown in the image below
Read more: Delete Blank Columns in Excel
Method 2:Using Ribbon Delete Option
Step 1: Select unnecessary columns or empty columns by pressing CTRL and Clicking on any desired column. Go to Home tab>> Delete>>Delete Sheet Columns.
The final image will be similar to the one shown in the image below
Method 3:Using Find & Select
Step 1: Go to Home>> Find & Select>> Click on Go to Special. A dialog window will pop up.
Step 2: Select Blanks then Click OK. Blank columns get selected.
Step 3: Right Click on any of the selected columns, a pop-up options menu will appear. Click Delete.
Step 4: A command dialog box pops up. Select Entire Column. Click OK.
To bring up the command dialog box press Keyboard Shortcut CTRL+ -.
The end result is shown below image
Similar Readings:
- How to Delete Unused Columns in Excel(Easiest 5 Methods)
- Delete Columns in Excel Without Affecting Formula (Two Ways)
- How to Delete Duplicate Columns in Excel (6 Ways)
- Delete Extra Columns in Excel (7 Methods)
Method 4:Using Function
In the case of deleting multiple blank columns, we can use the function COUNTA >> to show a specific text(True or False) >>Sorting (according to the text; True or False)>>Delete.
Step 1: Insert a helper row at the top of the dataset. And Clear the column names of Empty columns.
Step 2:EnterÂ
=COUNTA(A3:A1048576)=0
Step 3:Select the data range including the helper row. Click on Data Ribbon>> Select Sort. A window pops up.
Step 4:Click on Options(a command dialog box pop up)>> Select Sort left to Right>> Click OK.
Step 5:In that Sort window, Select ROW 2 (from Sort by drop-down menu )>>Keep Cell Values (from Sort On drop-down menu ) >> Select Largest to Shortest (from Order drop-down menu)
Step 6: Click OK. The result will be similar to the image below
Step 7: Select all the blank columns>> Right click>> Select Delete>> Select Delete Entire Column.Â
Step 8:Click OK. The outcome depict the below image
Method 5:Using VBA
i) Delete Multiple Blank Columns with a Header
Step 1: Press ALT + F11 to bring out the Visual Basic Editor.
Step 2:Go to Menu Bar, Click Insert >> Click Module.
Step 3:Copy the below Code & Paste >> in the Code Window.
Sub deleteblankcolwithheader()
'updateby Extendoffice
    Dim xEndCol As Long
    Dim I As Long
    Dim xDel As Boolean
    On Error Resume Next
    xEndCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    If xEndCol = 0 Then
        MsgBox "There is no data on """ & ActiveSheet.Name & """ .", vbExclamation, "Columns Delete Excel"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For I = xEndCol To 1 Step -1
        If Application.WorksheetFunction.CountA(Columns(I)) <= 1 Then
            Columns(I).Delete
            xDel = True
        End If
    Next
    If xDel Then
        MsgBox "All blank column(s) with only a header row have been deleted."   Â
Else
        MsgBox "There are no Columns to delete as each one has more data (rows) than just a header."
    End If
    Application.ScreenUpdating = True
End SubStep 4:Press F5 to execute the Code.
Step 5:A pop-up dialog box will appear. Click OK.
The result will be similar to the below image
ii)Delete Multiple Empty Columns Without a Header
Step1: Repeat Step 1 to 3 (from the above method)
Step 2:Copy the below Code & Paste >> in the Code Window.
Public Sub DeleteEmptyColumns()
    Dim SourceRange As Range
    Dim EntireColumn As Range
 On Error Resume Next
 Set SourceRange = Application.InputBox( _
        "Select a range:", "Delete Empty Columns", _
        Application.Selection.Address, Type:=8)
 If Not (SourceRange Is Nothing) Then
        Application.ScreenUpdating = False
     For i = SourceRange.Columns.Count To 1 Step -1
            Set EntireColumn = SourceRange.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
                EntireColumn.Delete
            End If
        Next
Application.ScreenUpdating = True
    End If
End SubStep 3:Press F5 to execute the Code.
Step 4:A pop-up dialog box will appear, Go to the worksheet & select the range you desire. Click OK.
The execution brings the result as in the image below
Conclusion
Deleting multiple columns; unnecessary, blank with or without header quite useful to organize a dataset. In this article, we showed the simplest tools offered in Excel to delete multiple columns and also VBA code to delete multiple columns on a criteria basis. I hope, you find these methods super easy & handy. Comment, if further clarifications are needed or you want to add something.
