Editor choice

How to Delete Multiple Columns in Excel (5 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

Deleting multiple columns; empty or unnecessary is quite a desirable task in Excel operations. If you are looking for special tricks to delete multiple Excel columns, you’ve come to the right place. We’ll show 5 suitable methods to delete multiple columns in Excel. This article will discuss every step of the methods. Let’s follow the complete guide to learn all of this.

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.

sample dataset to Delete Multiple Columns in Excel

You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Deleting Multiple Columns Through Context Menu in Excel

Here, we will demonstrate how to delete multiple columns through the context menu. Let’s say the Salary, Joining Month & Joining Date columns in my dataset are not needed. They must be removed.

📌 Steps:

  • First, select the three columns (Joining Month, Joining Date & Salary).
  • Right-click on any selected column. An options menu will pop up.
  • You can press CTRL & click on the columns you want to select.
  • Next, click Delete.

  • Therefore, the outcome will be similar to the one shown in the picture below.

using context menu to Delete Multiple Columns in Excel

  • Now, we will show how to delete random empty multiple columns.
  • Select random multiple empty columns by pressing CTRL, then click on the columns.

select the desired columns to Delete Multiple Columns in Excel

  • Then, right-click on the selected columns, and a window will pop up.
  • Next, click Delete. The outcome is shown in the image below.

 Delete Multiple Columns in Excel

Read More: How to Delete Blank Columns in Excel


2. Using Delete Sheet Columns Option in Home Tab for Removing Multiple Columns

In this second method, we will illustrate how to use the Delete Sheet Columns option in the Home tab in Excel. Let’s follow the following steps to do the task.

📌 Steps:

  • First, select unnecessary columns or empty columns by pressing CTRL and clicking on any desired column.
  • Go to the Home tab >> Delete >> Delete Sheet Columns.

use delete sheet columns option to Delete Multiple Columns in Excel

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


3. Deleting Specific Columns After Selecting Through Find & Select Feature

Now, we will show how to delete multiple columns using the Find & Select feature. To complete the task, let’s go through the steps below.

📌 Steps:

  • Go to Home >> Find & Select >> click on Go to Special.

use Find & Select feature to Delete Multiple Columns in Excel

  • A dialog window will pop up.
  • Select Blanks, then click OK.

select Blank option

  • All blank columns are selected.

get the blank columns

  • Next, right-click on any of the selected columns, and a pop-up options menu will appear. Click Delete.
  • A command dialog box pops up.
  • Select Entire Column and click OK.


4. Eliminating Specific Columns After Finding Through COUNTA Function

In this fourth method, we will show how to use the COUNTA function to delete multiple cells in Excel. Let’s go through the steps listed below to finish the task.

📌 Steps:

  • 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.
  • Insert a helper row at the top of the dataset.
  • Clear the column names of empty columns.
  • Next, write down the following formula in cell B3.
=COUNTA(B4:B1048576)=0
  • Press Enter and drag the Fill Handle icon towards the right.
  • True and False text will appear above each column.

use COUNTA function to Delete Multiple Columns in Excel

  • Select the data range including the helper row.
  • Next, click on Data Ribbon >> select Sort.

select SORT option to Delete Multiple Columns in Excel

  • A window pops up.
  • Click on Options (a dialog box pops up) >> Select Sort left to Right >> click OK.

  • In that Sort window, select Row 3 (from the Sort by drop-down menu).
  • Choose Cell Values (from Sort On drop-down menu ) >> select Largest to Shortest (from Order drop-down menu).

select order option to Delete Multiple Columns in Excel

  • Next, click OK. The result will be similar to the image below.

  • Select all the blank columns >> Right click >> Select Delete >> Select Delete Entire Column.
  • Finally, click OK.
  • As a result, the outcome depicts the below image.

 Delete Multiple Columns in Excel

Read More: How to Delete Multiple Columns in Excel with Condition


5. Applying Excel VBA to Delete Multiple Columns

Finally, we will demonstrate how to delete multiple columns using Excel VBA. Let’s complete the task by adhering to the below-listed instructions.

📌 Steps:

  • VBA has its own separate window to work with. You have to insert the code in this window too.
  • To open the VBA window, click the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group.

insert VBA window to Delete Multiple Columns in Excel

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window.
  • To insert a module for the code, go to the Insert tab on the VBA editor.
  • Then, click on Module from the drop-down.

insert module to Delete Multiple Columns in Excel

  • As a result, a new module will be created.
  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub delete2()
    Dim xEnd1Col As Long
    Dim I As Long
    Dim XDel1 As Boolean
    On Error Resume Next
    xEnd1Col = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    If xEnd1Col = 0 Then
        MsgBox "No data available""" & ActiveSheet.Name & """ .", vbExclamation, "delete Multiple columns"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For I = xEnd1Col To 1 Step -1
        If Application.WorksheetFunction.CountA(Columns(I)) <= 1 Then
            Columns(I).Delete
            XDel1 = True
        End If
    Next
    If XDel1 Then
        MsgBox "You have deleted all columns"
Else
        MsgBox "No remaining column"
    End If
    Application.ScreenUpdating = True
End Sub
  • Press F5 to execute the code.
  • A pop-up dialog box will appear. Click OK.

click on OK

  • Therefore, the result will be similar to the below image.

  • Now, we will show another VBA code to delete multiple empty columns without a header.
  • Repeat the above steps.
  • Then, copy the below code in the code window.
Public Sub Deleteing1()
    Dim Source1Range As Range
    Dim Entire1Column As Range
 On Error Resume Next
 Set Source1Range = Application.InputBox("You have to selecet range of data:", "Delete Multiple columns", Application.Selection.Address, Type:=8)
 If Not (Source1Range Is Nothing) Then
        Application.ScreenUpdating = False
        For I = Source1Range.Columns.Count To 1 Step -1
            Set Entire1Column = Source1Range.Cells(1, I).Entire1Column
            If Application.WorksheetFunction.CountA(Entire1Column) = 0 Then
                Entire1Column.Delete
            End If
        Next
Application.ScreenUpdating = True
    End If
End Sub
  • Next, save the code.
  • Afterward, close the Visual Basic window. After that press Alt + F8.
  • When the Macro dialogue box opens, select the desired macro and click on Run.

select macro to Delete Multiple Columns in Excel

  • A pop-up dialog box will appear, go to the worksheet and select the range you desire. Click OK.

  • Finally, the execution brings the result.

 Delete Multiple Columns in Excel


Download Practice Workbook


Conclusion

Deleting multiple columns; unnecessary, blank with or without headers 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 if you want to add something. Don’t forget to check our website for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Delete Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo