Editor choice

How to Delete Multiple Columns in Excel

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

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.

consecutive multiple columns

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 turnsturn to plus sign and drag the cursor over the columns.

Step 2: Click Delete. The outcome will be similar to the one shown in the picture below

delete output

ii) Deleting Random Empty Multiple Empty Columns 

Step 1: Select random multiple empty columns pressing CTRL then click on the columns.

random multiple empty column

Step 2: Right Click on the selected columns, a window will pop up then Click Delete. The outcome is shown in the image below

final output delete

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.

find & select

 

The final image will be similar to the one shown in the image below

final outcome of ribbon delete option

Method 3:Using Find & Select

Step 1: Go to Home>> Find & Select>> Click on Go to Special. A dialog window will pop up.

find & select

 

Step 2: Select Blanks then Click OK. Blank columns get selected.

find & select blank selection

Step 3: Right Click on any of the selected columns, a pop-up options menu will appear. Click Delete.

delete option

Step 4: A command dialog box pops up. Select Entire Column. Click OK.

select entire column

To bring up the command dialog box press Keyboard Shortcut CTRL+ -.

The end result is shown below image

final result find & select


Similar Readings:


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.

COUNTA function

Step 2:Enter 

=COUNTA(A3:A1048576)=0
 in A2 cell then drag fill handle towards the right. True & False text will appear above each column.

function applied

Step 3:Select the data range including the helper row. Click on Data Ribbon>> Select Sort. A window pops up.

sort selection

Step 4:Click on Options(a command dialog box pop up)>> Select Sort left to Right>> Click OK.

specials

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)

selection of options

Step 6: Click OK. The result will be similar to the image below

appllied function

Step 7: Select all the blank columns>> Right click>> Select Delete>> Select Delete Entire Column. 

delete entire column

Step 8:Click OK. The outcome depict the below image

result of function

Method 5:Using VBA

i) Delete Multiple Blank Columns with a Header

Step 1: Press ALT + F11 to bring out the Visual Basic Editor.

visual basic editor

Step 2:Go to Menu Bar, Click Insert >> Click Module.

module selection

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 Sub

Step 4:Press F5 to execute the Code.

Step 5:A pop-up dialog box will appear. Click OK.

option VBA

The result will be similar to the below image

vba result

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 Sub

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

vba option

The execution brings the result as in the image below

final result vba

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.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo