Table of Contents
To delete a column in VBA, you can use the “Columns.Delete” method within the worksheet object. This method takes a single argument, which is the column index you wish to delete. For example, to delete column C, you would use the code “ActiveSheet.Columns(3).Delete”. This code will delete the specified column, shifting any data to the left of the column to the right. Note that if any data is in the deleted column, that data will be lost.
You can use the following methods to delete columns in Excel using VBA:
Method 1: Delete One Column
Sub DeleteColumns()
Columns("C").Delete
End Sub
This particular example will delete column C from the current workbook.
Method 2: Delete All Columns in Range
Sub DeleteColumns()
Columns("B:D").Delete
End Sub
This particular example will delete all columns in the range B through D in the current workbook.
Method 3: Delete Several Specific Columns
Sub DeleteColumns()
Range("B:B, D:D").Delete
End Sub
This particular example will delete columns B and D in the current workbook.
The following examples show how to use each of these methods in practice with the following dataset in Excel:
Example 1: Delete One Column in VBA
We can create the following macro to delete only column C from our dataset:
Sub DeleteColumns()
Columns("C").Delete
End Sub
When we run this macro, we receive the following output:
Example 2: Delete All Columns in Range
We can create the following macro to delete all columns in the range from B to D:
Sub DeleteColumns()
Columns("B:D").Delete
End Sub
When we run this macro, we receive the following output:
Notice that each column in the range from B to D (the “Points”, “Assists”, and “Rebounds” columns) have been deleted from the dataset.
Example 3: Delete Several Specific Columns
We can create the following macro to delete columns B and D from the dataset:
Sub DeleteColumns()
Range("B:B, D:D").Delete
End Sub
When we run this macro, we receive the following output:
Notice that columns B and D (the “Points” and “Rebounds” columns) have been deleted from the dataset.