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

Implementing VBA macro 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 based on criteria in Excel with VBA macro.

Generic Syntax

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

VBA Macro to Delete Columns Based on Criteria in Excel: 8 Examples

In this section, you will learn how to delete columns based on cell value, based on another worksheet, and based on a table with VBA macro in Excel.

We will use the following dataset as an example.

Criteria 1: 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:

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

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

• Copy the following code and paste it into the code window.

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

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

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

The result is shown below.

Columns started with cell value 2 are deleted.

Criteria 2: VBA Macro to Delete Columns Based on Blank Cells in Excel

Steps to delete columns with blank cells 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.
• Copy the following code and paste it.

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 are deleted now.

Criteria 3: VBA Macro to Delete Columns that are Blank in Excel

Look at the following dataset consisting of blank columns. We will see how to delete the blank columns in Excel with VBA macro.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy and paste 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 are deleted.

Criteria 4: VBA Macro to Remove Columns that are Non-Adjacent in Excel

Steps to remove multiple non-adjacent 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.
• Copy the following code and paste it.

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

• Run the macro.

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

Criteria 5: VBA Macro to Delete Columns Based on Specific Worksheet in Excel

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.
• In the code window, copy the following code and paste it.

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

• Run the macro.

All the previous columns from D to F (Mar to May) from the New Column worksheet have been deleted.

Criteria 6: VBA Macro to Remove Column Based on a Table in Excel

Steps to delete the column from a table 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.
• Copy and paste 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) is deleted from the table.

Criteria 7: VBA Macro to Remove Multiple Columns Based on a Table in Excel

Steps to delete multiple columns from a table 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.
• 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

• Run the macro.

Columns 5 and 6 (Apr and May) have been deleted from the table.

Read More: How to Delete Multiple Columns by Number Using VBA in Excel

Criteria 8: VBA Macro to Delete Columns from a Table Based on Column Header in Excel

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.
• Copy and paste the below 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 is deleted from the table.

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

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to delete columns based on 8 different criteria 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 Article

• How to Delete Column and Shift Left Using VBA in Excel