Macro to Delete Columns in Excel (10 Methods)

When we have a large amount of data with lots of unnecessary columns in our Excel worksheet, it is hard to understand sometimes. If we remove the columns that we don’t need, then it becomes quite easier to understand the sheet later. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to delete columns with the VBA macro in Excel.


Download Workbook

You can download the free practice Excel workbook from here.


Generic Syntax

The generic syntax to delete columns in Excel using VBA is,

Columns(column number/ column address).Delete

10 Methods with VBA Macro to Delete Columns in Excel

In this section, you will learn how to delete a single column, multiple adjacent and non-adjacent columns, blank columns, columns with blank cells, columns from another worksheet, columns from a table and many more criteria with VBA in Excel.

Dataset of VBA Macro to Delete Columns in Excel

We will use the above dataset as an example.


1. Embed VBA to Delete Single Column in Excel

Steps to delete a single column in Excel with VBA are given below.

Steps:

  • In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • Next, in the pop-up code window, from the menu bar, click Insert -> Module.

  • Then, copy the following code and paste it into the code window.
Sub DeleteCol()
    Columns(5).Delete
End Sub

Your code is now ready to run.

VBA Macro to Delete Single Columns in Excel

  • Now, 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.

After the code execution, look at the image below to see the result.

As a result, Column 5 (Apr) is deleted from the dataset.

  • If you want to delete a column based on the column address, then instead of the column number pass the column address inside the parentheses. Then the code will look like this,
Sub DeleteCol()
    Columns(E).Delete
End Sub

This piece of code will delete the E column (Apr) from the dataset.

Related Content: How to Delete Infinite Columns in Excel (4 Methods)


2. Apply VBA Macro to Remove Multiple Columns in Excel

Steps to remove multiple columns in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteMCol()
    Columns("C:E").Delete 'delete cells from range C to E
End Sub

Your code is now ready to run.

VBA Macro to Delete Multiple Columns in Excel

  • Later, Run the macro.

As a result, all the previous cells from C to E (Feb to Apr) have been deleted.

  • This process can also be done with the Range The code to delete multiple columns using Range is shown below,
Sub DeleteMCol()
    Range("C:E").Delete
End Sub

This will also delete cells from C to E (Feb to Apr).

Read More: How to Delete Multiple Columns by Number Using VBA in Excel (4 Ways)


3. Implement Macro to Remove Multiple Non-Adjacent Columns

Steps to remove multiple non-adjacent columns in Excel with VBA are given below.

Steps:

  • As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteNAdjCol()
    Range("C:E, G:I").Delete
End Sub

Your code is now ready to run.

VBA Macro to Delete Multiple non adjacent Columns in Excel

  • Now, Run the macro.

Finally, all the previous columns from C to E (Feb to Apr) and G to I (Jun to Aug) have been deleted.

Related Content: How to Delete Multiple Columns in Excel


4. Attach VBA Code to Delete Blank Columns in Excel

Look at the following dataset consisting of blank columns.

Dataset of VBA Macro to Delete Blank Columns in Excel

We will see how to delete these blank columns in Excel with VBA macro.

Steps:

  • First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Second, in the code window, copy the following code and paste it.
Sub DeleteBlankCol()
    Dim i As Integer
    For i = 1 To 4
    Columns(i + 1).Delete
    Next i
End Sub

Your code is now ready to run.

VBA Macro to Delete Blank Columns in Excel

  • Third, Run the macro.

Finally, all the blank columns are deleted.

Read More: Can’t Delete Blank Columns in Excel (3 Problems & Solutions)


5. Embed Macro to Erase Columns with Blank Cells in Excel

Steps to delete columns with blank cells in Excel with VBA are given below.

Steps:

  • As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteBlankCells()
    Range("A1:J10").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireColumn.Delete
End Sub

Your code is now ready to run.

VBA Macro to Delete Columns with Blank Cellsin Excel

  • Now, Run the macro.

Finally, all the columns carrying blank cells are deleted now.

Read More: Delete Blank Columns in Excel (3 Ways)


Similar Readings:


6. Implement VBA Macro to Delete Columns Based on Cell Value in Excel

Steps to delete columns based on cell value in Excel with VBA are given below.

Steps:

  • First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteColumnsBasedOnCellValue()
Dim iCol As Long
Dim iWrk As Long
iCol = 10
For iWrk = iCol To 1 Step -1
    If Cells(1, iWrk) = 2 Then
        Columns(iWrk).Delete
    End If
Next
End Sub

Your code is now ready to run.

VBA Macro to Delete Columns in Excel cell value

This code will delete the columns that start with cell value 2.

  • Now, Run the macro code. The result is shown below.

As a result, columns started with cell value 2 (Feb, Sep) are deleted.

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


7. Implement Macro to Delete Column from a Specific Worksheet

Steps to delete the column from a specific worksheet in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteColFromSheet()
    Worksheets("New Column").Select
    Columns("D:F").Delete
End Sub

Your code is now ready to run.

VBA Macro to Delete Columns from different sheet in Excel

  • Later, Run the macro.

VBA Macro to Delete Columns from specific sheet in Excel

As a result, all the previous columns from D to F (Mar to May) from the “New Column” worksheet have been deleted.

Related Content: How to Delete Columns without Losing Formula in Excel (3 Easy Steps)


8. Apply Macro to Remove Column from a Table in Excel

Steps to delete the column from a table in Excel with VBA are given below.

Steps:

  • Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Secondly, in the code window, copy the following code and paste it.
Sub DeleteColTable()
    Dim srcTable As ListObject
    Dim srcSheet As Worksheet
    'Set the table from which column is to be deleted
    Set srcTable = ActiveSheet.ListObjects("ExTable")
    'Set the sheet that contains the table
    Set srcSheet = Sheet8
    With srcSheet
        srcTable.ListColumns(5).Delete
    End With
End Sub

Your code is now ready to run.

VBA Macro to Delete Columns from table in Excel

  • Thirdly, Run the macro.

Result of VBA Macro to Delete Columns from table in Excel

Lastly, Column 5 (Apr) is deleted from the table.

Read More: How to Delete Duplicate Columns in Excel (6 Ways)


9. Embed VBA to Eliminate Multiple Columns from a Table in Excel

Steps to delete multiple columns from a table in Excel with VBA are given below.

Steps:

  • First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteMColTable()
    Dim srcTable As ListObject
    Dim srcSheet As Worksheet
    Dim i As Integer
    'Set the table from which column is to be deleted
    Set srcTable = ActiveSheet.ListObjects("ExTable2")
    'Set the sheet that contains the table
    Set srcSheet = Sheet9
    'Run the loop twice as we need to delete 2 columns
    For i = 1 To 2
        With Source
            srcTable.ListColumns(5).Delete
        End With
    Next i
End Sub

Your code is now ready to run.

VBA Macro to Delete multiple Columns from table in Excel

  • Later, Run the macro.

As a result, Columns 5 and 6 (Apr and May) have been deleted from the table.

Read More: How to Delete Columns in Excel That Go on Forever (6 Ways)


10. Attach Macro to Exclude Column from a Table Based on Column Header

Steps to delete the column from a table based on the column header in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub DeleteColHeaderTable()
    Dim srcTable As ListObject
    Dim srcSheet As Worksheet
    Dim i As Integer
    Dim colName As String
    'Set the table from which column is to be deleted
    Set srcTable = ActiveSheet.ListObjects("ExTable3")
    'Set the sheet that contains the table
    Set srcSheet = Sheet10
    'Case sensitive
    colName = "Apr"
    'Loop through all the columns in the table
    For i = 1 To srcTable.ListColumns.Count
        With srcSheet
            'Check the column header
            If srcTable.ListColumns(i).Name = colName Then
                srcTable.ListColumns(i).Delete
                Exit For
            End If
        End With
    Next i
End Sub

Your code is now ready to run.

VBA Macro to Delete Columns from table based on header in Excel

  • Next, Run the macro.

Consequently, Column Header named Apr is deleted from the table.

Read More: How to Delete Columns Based on Header Using VBA in Excel


Conclusion

To conclude, this article showed you 10 effective methods on how to delete columns in Excel with VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles

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