In this article we will see how to delete columns in Excel using VBA.

Example 1: Delete an entire column in a sheet using column number:

Say, the Excel data that is of the form below and you need to delete the column for May-18 i.e column G

Here is the code. We need to specify the column number to be deleted and use .EntireColumn.Delete to delete the entire column

Sub delCol()
    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheet1
    sourceSheet.Columns(7).EntireColumn.Delete
End Sub

This is how the data will look like after executing the above code

Example 2: Delete an entire column in a sheet using column letter:
You can also use column letter instead of column number to delete it. So, the end-result in the above example can also be achieved using the code below:
Sub delColUsingLetter()
    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheet1
    sourceSheet.Columns("G").EntireColumn.Delete
End Sub

Example 3: Delete multiple columns in a sheet:
You can also delete multiple columns in a single delete statement. Here is the code that will delete columns D to F (4 to 6):
Sub delCol()
    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheet1
    sourceSheet.Columns("D:F").EntireColumn.Delete
End Sub

The output will look like this

Example 4: Delete non adjacent multiple columns in a sheet:
It is not necessary that the columns be adjacent in order to delete them in a single statement. Here is an example where multiple non adjacent columns are deleted
Sub delCol()
    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheet1
    sourceSheet.Range("D:E, H:H, J:K").EntireColumn.Delete
End Sub

Note that we have used “Range” to specify the columns instead of “Columns” in this case.
After running the code the data will look like this.

Example 5: Delete a column in a table:
The data (starting point) for this example is a table as seen in the snapshot below. The name of the table is “Table1”

If you want to delete the Address column (i.e. Column F of the sheet), you need to use the code below:
Sub delColTable()

    Dim tbl As ListObject
    Dim sourceSheet As Worksheet

    'Set the table from which column is to be deleted
    Set tbl = ActiveSheet.ListObjects("Table1")

    'Set the sheet that contains the table
    Set sourceSheet = Sheet2

    With sourceSheet
        tbl.ListColumns(5).Delete
    End With

End Sub

An important thing to note here is that while deleting a column from a table, you need to specify the column number relative to the first column of the table.  Column “No” is the first column, “Title” the second, and so on. In our case we want to delete the 5th column of the table (6th of the sheet). So, we have used “ListColumns(5)”
This is how the table will look like after execution of the code

Example 6: Delete multiple columns in a table:
To delete columns say, 5 and 6 from the table, we can use a for loop as given below
Sub delColsTable()

    Dim tbl As ListObject
    Dim sourceSheet As Worksheet
    Dim i As Integer

    'Set the table from which column is to be deleted
    Set tbl = ActiveSheet.ListObjects("Table1")

    'Set the sheet that contains the table
    Set sourceSheet = Sheet2

    'Run the loop twice as we need to delete 2 columns
    For i = 1 To 2
        With Source
            tbl.ListColumns(5).Delete
        End With
    Next i

End Sub

As we are going to delete 2 columns, the for loop has been iterated twice. A point to note here is that, in both the iterations we are deleting column 5. This is because, after the first execution of the delete column statement, column 6 will be shifted left to the position of 5. Hence, on the second execution, the city column is now at position 5 and deleted correctly.
This is useful when you need to delete a particular column and another column adjacent to it
The same result can be achieved without using the for loop as below:
Sub delColsTable()

    Dim tbl As ListObject
    Dim sourceSheet As Worksheet
    Dim i As Integer

    'Set the table from which column is to be deleted
    Set tbl = ActiveSheet.ListObjects("Table1")

    'Set the sheet that contains the table
    Set sourceSheet = Sheet2

    With sourceSheet
        tbl.ListColumns(6).Delete
        tbl.ListColumns(5).Delete
    End With

End Sub

Note that we are deleting column 6 first, so that the position of the second column to be deleted does not change. The output will look like this:

Example 7: Delete a column in a table based on the column header:
In this final example, we will see how to delete a column from a table when you know the name of the column header and not it’s position. So, we will be deleting the column “City” in the code below.
Sub delOnColHeaderOfTable()

    Dim tbl As ListObject
    Dim sourceSheet As Worksheet
    Dim i As Integer
    Dim colNameToDel As String

    'Set the table from which column is to be deleted
    Set tbl = ActiveSheet.ListObjects("Table1")

    'Set the sheet that contains the table
    Set sourceSheet = Sheet2

    'Case sensitive
    colNameToDel = "City"

    'Loop through all the columns in the table
    For i = 1 To tbl.ListColumns.Count
        With sourceSheet
            'Check the column header
            If tbl.ListColumns(i).Name = colNameToDel Then
                tbl.ListColumns(i).Delete
                Exit For
            End If
        End With
    Next i
End Sub

Here, we run the for loop through all the columns of the table. The column count in the table is given by “tbl.ListColumns.Count”. As we have to delete a single column, we have started the counter at 1. If you need to delete multiple columns, you need to start from the last column. Also remove the exit for statement in that case
The if condition checks whether the name of the current column (using tbl.ListColumns(i).Name) matches the name of the column to be deleted. If so, that column is deleted.
Thus, we have seen different ways to delete a single and multiple columns from a sheet or a table.
For further information on Tables, refer to the article – VBA, Word Table Insert/Remove Rows/Columns
See also:

How to delete rows using VBA