Normally in excel worksheet we have two different methods to delete columns one being the keyboard shortcut and another by using the right-click and insert method but in VBA we have to use delete command and entire column statement to delete any column together, the trick to this is that if we need to delete a single column we give a single column reference but for the multiple columns we give multiple column references.
Excel VBA Delete Column
We perform many actions in excel like cut, copy, paste, add, delete, insert, and many such things we do on a regular basis. We can use all of these actions using VBA Coding. One of the important concepts we need to learn is in VBA is the “deleting column.” In this article, we will show you how to use this Delete Column option in VBA.
What Does Delete Column Do in Excel VBA?
As the name says, it will delete the specified column. To perform this task, we need to first identify which column we need to delete. The selection of columns to delete is different from one scenario to another, so we will cover some of the important and often faced scenarios in this article.
Deleting the columns is easy. First, we need to use the COLUMNS property to select the column, so below is the syntax of the Column Delete method in VBA.
Columns (Column Reference).Delete
So we can construct the code like this:
Columns (2).Delete or Columns (“B”).Delete
This will delete column number 2, i.e., Column B.
If we want to delete multiple columns, we cannot enter columns. We need to reference the columns by column headers, i.e., alphabets.
This will delete the column from A to D, i.e., the first 4 columns.
Like this, we can use the “Delete Column” method in VBA to delete the particular columns. In the below section, we will see more examples to understand it better. Read on.
Examples of Excel VBA Delete Column Method
Below are the examples to delete columns using VBA.
Example #1 – Using Delete Method
Assume you have the datasheet, something like the below.
If we want to delete the column month “Mar,” first select the columns property.
Sub Delete_Example1() Columns( End Sub
Mention the column number or alphabet. In this case, it is either 3 or C.
Sub Delete_Example1() Columns(3). End Sub
Use the Delete method.
Sub Delete_Example1() Columns(3).Delete End Sub
Or you can enter the column address like this.
Sub Delete_Example1() Columns("C").Delete End Sub
Run this Code using the F5 key, or you can run manually and see the result.
Both the codes will do the same job of deleting the mentioned column.
If we want to delete multiple columns, we need to mention them in the alphabet. We cannot use column numbers here.
If we want to delete the columns 2 to 4, we can pass the code like the below.
Sub Delete_Example1() Columns("C:D").Delete End Sub
Run this code manually through the run option or press the F5 key. This will delete the columns “Feb,” “Mar,” and “Apr.”
Example #2 – Delete Columns with Worksheet Name
The above is an overview of how to delete columns using VBA code. However, that is not the good practice of deleting columns. Without referring the worksheet name, it is dangerous to blindly delete the column.
If you have not mentioned the worksheet name, then whichever sheet is active, it will delete columns of that sheet.
First, we need to select the worksheet by its name.
Sub Delete_Example2() Worksheets("Sales Sheet").Select End Sub
After selecting the sheet, we need to select the columns. We can also select the columns by using the VBA RANGE object as well.
Sub Delete_Example2() Worksheets("Sales Sheet").Select Range("B:D").Delete End Sub
This will delete the columns B to D of the worksheet “Sales Sheet.” For this code, it doesn’t matter which is active. Still, it will delete the mentioned columns of that sheet only.
We can also construct the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. in the single line itself.
Sub Delete_Example2() Worksheets("Sales Sheet").Range("B:D").Delete End Sub
This also deletes the columns “B to D” without selecting the worksheet “Sales Sheet.”
Example #3 – Delete Blank Columns
Assume you have data that has alternative blank columns like the below.
So, delete every alternative column we can use the below code.
Sub Delete_Example3() Dim k As Integer For k = 1 To 4 Columns(k + 1).Delete Next k End Sub
Run this code using the F5 key or manually. Then, it will delete all the alternative blank columns, and our data will look like this.
Example #4 – Delete Blank Cells Columns
Now, look at this example. There are certain situations where we need to delete the entire column if any of the blank cells are found in the data range. Consider the below data for an example.
All the yellow-colored cells are blank. So here, my requirement is to delete all the blank cells columns. The below code will do that.
Sub Delete_Example4() Range("A1:F9").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireColumn.Delete End Sub
Let me explain this code line by line for you.
Our data is there from A1 to F9, so first, I need to select that range, and the below code will that.
In this selected range of cells, I need to select the cells which are blank. So to select a blank cell, we need to special cells property, and in that property, we have used cell type as blank.
Next, it will select all the blank cells, and in the selection, we are deleting the entire column of the selection.
So our end result will look like this.
Wherever it has found the blank cell, it has deleted those blank cells entire column.
You can download this Excel VBA Delete Column here – VBA Delete Column Template
This has been a guide to VBA Delete Column. Here we learn 4 different methods to delete columns using Excel VBA code along with practical examples and downloadable codes. Below are some useful excel articles related to VBA –