How to Delete a Column using VBA in Excel

This tutorial will teach us to write a VBA code (macro) to delete a column. We will explore other ways to use the code and delete multiple columns.

Delete a Single Column with VBA

1. First, enter the Column property to specify the column number you want to delete.
2. Next, enter a dot (.).
3. Enter the “EntireColumn” property to refer to the entire column.
4. In the end, enter a dot (.) and then enter the delete method to tell the code to delete the column.

Columns(1).EntireColumn.Delete

Apart from this, you can also use other methods to delete a column using a VBA code.

Range("A1").EntireColumn.Delete
Selection.EntireColumn.Delete

You can use the RANGE object to define a cell to delete the column, or you can also use the selection property to delete the column for the selected cell.

Delete Multiple Columns using a VBA Code.

Just like a single column, you can also delete multiple columns. In this case, you need to specify the address of those columns.

In the above example, we have a code with two lines that delete multiple columns.

• The first line of code deletes columns A, B, and C. The range we have specified is A1 to C1.
• The second line of the code deletes columns A and B only. You have specified the A1 and C1, which means two columns.

Range("A1:C1").EntireColumn.Delete
Range("A1", "C1").EntireColumn.Delete

Important Note – Delete multiple columns using a sequence from the last column to the first column. If you want to delete columns C, E, and H, delete the H column first, then the E, and then the C. Because when you delete a column, the column ahead takes its place. If you delete the second column, the third column will take its place. And when you delete column four after that, you are deleting the 5th column.

Delete All the Columns in the Sheet

You can also delete all the columns from a sheet using the “Cells” property. This property helps you refer to all the cells in the sheet.

Then, you can use the EntireColumn property to refer to all the columns and then delete the method to delete all the columns.

Cells.EntireColumn.Delete

Delete Alternate Columns

Let’s say you want to delete alternate columns (every 2^nd, 5^th, or 10^th column) from a sheet. Well, for this, you need to use code with a loop.

Before you understand this code, you need to know two things:

• This code works backwards. It deletes the columns from right to left.
• It deletes every second column from the sheet.

Alright, now, let’s understand this code in detail.

In this code, we have used a variable (iColumn) to count the total number of columns to store it.

After that, we used the VBA FOR NEXT LOOP to loop to every second column of the sheet.

But here, you need to understand that this code starts deleting the column from the last column instead of the first (as I mentioned) earlier. We have five columns in the selection, and this code deletes 3 out of them (5^th, 3^rd, and 1^st).

As you can see, we have used the -2 in the steps to delete every second column from the selection. If you want to delete every third, use the -3 in the steps.

Sub vba_delete_column2()
Dim iColumn As Integer
Dim i As Integer
iColumn = Selection.Columns.Count
For i = iColumn To 1 Step -2
    Selection.Columns(i).EntireColumn.Delete
Next i
End Sub