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

Method 1 – VBA Macro to Delete Columns Based on Cell Value in Excel

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 select Run -> Run Sub/UserForm from the menu bar. Click on the small play icon in the sub-menu bar to run the macro.

The result is below.

Columns started with cell value 2 are deleted.

Method 2 – VBA Macro to Delete Columns Based on Blank Cells in Excel

Steps:

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

Method 3 – VBA Macro to Delete Columns that are Blank in Excel

Steps:

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

Method 4 – VBA Macro to Remove Columns that are Non-Adjacent in Excel

Steps:

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

Method 5 – VBA Macro to Delete Columns Based on Specific Worksheet in Excel

Steps:

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

Method 6 – VBA Macro to Remove Column Based on a Table in Excel

Steps:

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

Method 7 – VBA Macro to Remove Multiple Columns Based on a Table in Excel

Steps:

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

Method 8 – VBA Macro to Delete Columns 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.
• 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.

Download Workbook

You can download the free practice Excel workbook from here.

Related Article

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