VBA Delete Column

What is Excel VBA Delete Column?

Data manipulation is a regular task in Excel, including deleting a column or columns. When we automate tasks in Excel, we may delete a few columns, and knowing the logic behind this is a vital part of automation. VBA Delete Column is deleting a specific column and getting rid of its data so that the right side column will take the place of the deleted column. For example, look at the following data in Excel.

Assume we must delete the column “C;” we can use the following code.

Sub Delete_Column_Intro()
Range(“C:C”).Delete
End Sub

Once we run the code, it will delete the column “C.” As we can see there is no data in column “C” since we used the delete column method it has deleted all the data in the deleted column.

What Does Delete Column Do in Excel VBA?

As the name suggests, Delete Column deletes a given column. The selection of the required column will be different based on the circumstances; hence in this article, we will convert some of the common and real-time examples.

To delete a column, we must specify the column to be deleted using one of the following methods.

• COLUMNS property.
• RANGE Object

For example, we can use the following structure to reference any column using the VBA COLUMNS property.

Columns(2).Delete (This is the VBA delete column by number)

OR

Columns(“B”).Delete

We can use the RANGE object to reference the column.

Range(“B:B”).Delete

• As we can see in both methods, we first referenced the columns; then, we used the Delete method to delete them.
• In case of continuous multiple columns to be deleted, we must reference the start and end column names.
• For example, if we want to delete columns B, C, and D, we can reference the column as follows:
• Range(“B:D”).Delete (This is the VBA Delete column range)

Column “B” is the starting point, and column “D” is the endpoint. In between, “C” is included.

How to Delete Column in VBA?

Let us show you a step-by-step approach to applying the VBA Delete Column method in Excel. For this example, use the following data in Excel.

Assume we must delete the status column (column D), then we can follow the steps listed below.

Step 1: Start the sub-procedure by naming the macro.

Step 2: Now, we must reference the column which is to be deleted. Hence use either the COLUMNS property or the RANGE object.

Step 3: Since we are deleting the column “D,” reference this column using the column name inside the double quotes.

Step 4: Once the required column is entered, close the bracket.

Step 5: Now, we must choose this column’s “Delete” method. To access the “Delete” method enter a dot.

One of the limitations of using the COLUMNS property instead of the RANGE object is that we do not see the IntelliSense list once we enter the dot. Hence, we always recommend you use the RANGE object to reference columns.

Step 6: Enter the “Delete” method after entering the dot.

That’s it.

Let’s execute the code, and it will delete the referenced column.

As we can see, the status column is deleted, and the column to the immediate right takes over the place of the deleted column.

The column “Cost,” which was in column “E” before the deletion happened, moves one column to the left and takes over column “D” place.

Examples of Excel VBA Delete Column Method

Let us show you some case studies to apply the VBA Delete Column method in Excel. Let’s follow the examples listed below.

Example #1: Using Delete Method

We will use the following data to apply the VBA Delete Column method.

Assume we must delete column “D.” We can use the following steps.

Step 1: First, we must reference the column name. Hence, we must either use the COLUMNS property or the RANGE object. Because the IntelliSense list is available with the RANGE object, let’s use the RANGE object to reference the column.

Step 2: Since we are deleting the entire column, enter the column address in double quotes.

Step 3: Once the column address is given, enter a dot, and it will show the list of properties and methods associated with the VBA RANGE object.

Step 4: From the IntelliSense list, choose the “Delete” method.

Now, let’s execute the code. It should delete column “D.”

As we can see in column “D,” there was “Mar” data, which has been deleted and replaced by the immediate right column, i.e., the APR data column.

Similarly, all the subsequent columns from the right side moved one column to the left.

Example #2: Delete Column with Specific Header

In the previous example, we deleted the column “D,” and the immediate right columns replaced it. However, rerunning the code will delete the current “D” column, i.e., the “Apr” column.

To avoid these problems, we can use the header-based logic and then delete the column if the header is as per the given name.

For example, let’s look at the original data we had before applying the VBA Delete Column method.

We must delete column “D,” and its “D” header is “Mar.” We can apply a logical condition to check if the column “D” header equals “Mar” or not; if yes, we can delete the column or show a user-friendly message.

The following code can be used to check the header name and delete the column if it matches the given header name.

Sub Delete_Column_Ex2()
‘Define a variable to find store the header name
Dim Header_Name As String
‘Set the header name to the variable
Header_Name = “Mar”
‘Apply logical condition to check if the column “D” header value is “Mar”
‘If it matches then delete the column
If Range(“D1”).Value = Header_Name Then
Range(“D:D”).Delete
Else
MsgBox “The column ‘D’ header value is something different”
End If
End Sub

Let us execute the code, which will delete the column “D.”

Since the column “D” header was “Mar,” it has deleted the column “D.” Let’s execute the code again and see what happens.

Instead of deleting the column, first, it checks for the column header name. Since the column “D” header name is not “Mar,” it has not applied the VBA Delete Column method. Instead, it shows the message saying, “The column ‘D’ header value is something different.”

It makes the code more flexible and avoids unnecessary deletion of columns.

Example #3: VBA Delete Column Based on Header

The previous two codes will delete the column “D” from any active worksheet. However, when running this code, we must ensure which worksheet we are in while executing the code.

For example, assume we need to delete the column in the worksheet “Sales,” then we can select the worksheet “Sales” then execute the VBA Delete Column method. Look at the following code.

Sub Delete_Column_Ex3()
‘Define a variable to find store the header name
Dim Header_Name As String
‘Set the header name to the variable
Header_Name = “Mar”
‘Select the worksheet “Sales” before execute the VBA Delete Column code
Worksheets(“Sales”).Select
‘Apply logical condition to check if the column “D” header value is “Mar”
‘If it matches then delete the column
If Range(“D1”).Value = Header_Name Then
Range(“D:D”).Delete
Else
MsgBox “The column ‘D’ header value is something different”
End If
End Sub

As we can see, the yellow-colored lines will activate the worksheet “Sales” and then execute the VBA Delete Column code.

Example #4: Delete Blank Columns

Dealing with blank columns is often very difficult. Hence deleting those blank columns will be one of the data manipulation techniques we must be aware of.

For example, look at the following data in Excel.

We have some blank columns in columns C, G, and I.

Here we must dynamically use the dynamic code to delete all the blank columns. The following code will dynamically delete all the blank columns.

Sub Delete_Column_Ex4()
‘Define variable to find the last used column in first row
Dim LC As Long
‘Find the last used column in row #1
LC = Cells(1, Columns.Count).End(xlToLeft).Column
‘Define a variable to use for loop
Dim k As Long
‘Define a variable to get the entire column filled cells count
Dim Column_Count As Long
For k = 1 To LC
‘Find the column cells count
Column_Count = WorksheetFunction.CountA(Columns(k))
‘Check if the current looping column count is 0
‘If count is 0 then delete that entire column
If Column_Count = 0 Then
Cells(1, k).EntireColumn.Delete
‘Once the deletion of column happens one column eliminated
‘Hence we need to reset the last used column variable LC by 1
LC = LC – 1
‘At any given point of time if hte looping value is less than last used column then exit the loop
If LC < k Then Exit For
End If
Next k
End Sub

Execute this code. It will delete all the blank columns.

Example #5: Delete Blank Cells Columns

Deleting blank columns is one way, and dealing with empty cells columns is another. For example, look at the following data in Excel.

We have some of the blank cells in some columns. The requirement is to delete the entire column if any cells are empty.

The following code will check for the blank cells in each column and delete the entire column if there are any blank cells in it.

Sub Delete_Column_Ex5()
‘Define a variable to find the last used row
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to find the last used column
Dim LC As Long
‘Find the last used row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
‘Define a varaible to set the dynamic range
Dim Rng As Range
‘Set the range using the last used row and last used column
Set Rng = Cells(1, 1).Resize(LR, LC)
‘Use the special cells method and choose the blank cells and insert dummy value “Hii”
Rng.SpecialCells(xlCellTypeBlanks).Value = “Hii”
‘Start the loop of each column
Dim k As Long
For k = 1 To LC
‘Define variable to count dummay value
Dim Dummy_Count As Long
Dummy_Count = WorksheetFunction.CountIf(Columns(k), “Hii”)
‘If the dummy value count in the looping column is greter than 1 then delete that column
If Dummy_Count > 0 Then
Cells(1, k).EntireColumn.Delete
LC = LC – 1
If LC < k Then Exit For
End If
Next k
End Sub

This code will check for the blank cells in each column and insert a dummy value called “Hii.” Once it inserts a dummy value, the FOR NEXT loop will go through each column and check for the dummy value count.

The IF logical condition will check for the dummy value count; if the count is greater than one, it will delete that entire column; otherwise, it will move on to the next column. The result of this code will be as follows.

Important Things to Note

• With the COLUMNS property, we do not see the IntelliSense list; hence, using the RANGE object as an alternative is recommended, especially if you are starting the VBA coding.
• When multiple cells are selected from multiple columns, we cannot perform the deletion of all the selected cells’ columns; it will throw an error.
• When referencing any column using the RANGE object, we must give the complete column address in double-quotes.
• When we delete any column, the column to its immediate right will take over the deleted ‘s place. Hence, we must be careful while running the deletion of the column code for the second time.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Delete Column, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Delete Column. Here we learn how to delete excel column using VBA code with examples & downloadable excel template. You can learn more from the following articles –

• VBA Hide Columns
• VBA Insert Columns
• VBA Advanced Filter