VBA to Delete Column in Excel (9 Criteria)

Generic Syntax

The generic syntax to delete single or multiple columns in Excel using VBA is:

We will use the following dataset as an example.

Method 1 – VBA to Delete Single Column in Excel

Steps:

• Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• From the menu bar, click Insert -> Module.

• Enter the following code.

Sub DeleteCol()
Columns(5).Delete
End Sub

Your code is now ready to run.

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

Column 5 (Apr) will be deleted from the dataset.

• You can delete a column based on the column address instead of the column number. Insert the column address instead of the column number inside the parentheses. The code will look like this,

Sub DeleteCol()
Columns(E).Delete
End Sub

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

Method 2 – VBA to Remove Multiple Columns in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

Sub DeleteMCol()
Columns(“C:E”).Delete ‘//delete cells from range C to E
End Sub

• Run the macro.

The columns from C to E (Feb to Apr) will be 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 delete columns C to E (Feb to Apr).

Read more: VBA Macro to Delete Columns Based on Criteria in Excel

Method 3 – VBA to Remove Multiple Non-Adjacent Columns in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

Sub DeleteNAdjCol()
Range("C:E, G:I").Delete
End Sub

• Run the macro.

The columns from C to E (Feb to Apr) and G to I (Jun to Aug) will be deleted.

Method 4 – VBA to Delete Columns with Blank Cells in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

Sub DeleteBlankCells()
Range("A1:J10").Select '//range of the dataset
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireColumn.Delete
End Sub

• Run the macro.

All the columns carrying blank cells will be deleted.

Method 5 – VBA to Delete Blank Columns in Excel

The following sample dataset has some blank columns.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

Sub DeleteBlankCol()
Dim i As Integer
For i = 1 To 4
Columns(i + 1).Delete
Next i
End Sub

• Run the macro.

All the blank columns will be deleted.

Method 6 – VBA to Delete Column from a Specific Worksheet in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

Sub DeleteColFromSheet()
Worksheets("New Column").Select '// set the sheet name
Columns("D:F").Delete
End Sub

• Run the macro.

The previous columns from D to F (Mar to May) from the New Column worksheet will be deleted.

Read More: How to Delete Columns with Specific Text in Excel

Method 7 – VBA to Remove Column from a Table in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

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

• Run the macro.

Column 5 (Apr) will be deleted from the table.

Method 8 – VBA to Remove Multiple Columns from a Table in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

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

• Run the macro.

Columns 5 and 6 (Apr and May) will be deleted from the table.

Method 9 – VBA to Delete Column from a Table Based on Column Header in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Enter the following code.

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

• Run the macro.

The column header named Apr will be deleted from the table.

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

Download Workbook

You May Also Like to Explore

• How to Delete Every Other Column in Excel
• How to Delete Unused Columns in Excel
  
• How to Delete Infinite Columns in Excel
• How to Delete Hidden Columns in Excel
• How to Delete Column in Excel Without Affecting Formula
• How to Delete Multiple Columns in Excel with Condition
• How to Delete Multiple Columns by Number Using VBA in Excel
  
• How to Delete Column and Shift Left Using VBA in Excel
  
• [Solved!] Can’t Delete Extra Columns in Excel