How to delete columns in Excel using VBA
In this article we will see how to delete columns in Excel using VBA.
Contents
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 SubThis 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 SubExample 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 SubThe 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 SubNote 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 SubAn 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 SubAs 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 SubNote 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 SubHere, 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:
One thought on “How to delete columns in Excel using VBA”