Updated May 26, 2023
VBA Delete Column
ome common operations performed in Excel include copy, paste, cut, delete, and insert. The easy method to do this is, using the shortcut keys or built-in functions. But VBA is the solution to serve the operation in a single click or automatically. We can automate these tasks using Excel macros. VBA also provides different methods similar to Excel functions. This performs these tasks smoothly in VBA.
Columns delete method is used to delete single or multiple columns in Excel VBA. The delete property of columns is used along with the column index.
Syntax to Delete Column in Excel VBA
The syntax to delete a column in Excel is as below.
Columns (Column Reference). Delete
- Where column reference is the column number you want to delete.
- Columns([RowIndex], ColumnIndex]) here, the column range is also accepted.
How to Delete Columns in Excel Using VBA?
We will learn how to Delete a Column in VBA with a few examples in Excel.
VBA Delete Column – Example #1
The attendance database provides office leaving time for some employees. It’s the data captured for one week, from Monday to Friday.
We want to delete column Friday.
Follow the below steps to delete a column in Excel.
Step 1: First, we can create a simple function as dele() since the delete is a keyword that is not preferred.
Code:
Private Sub dele() End Sub
Step 2: Now, let’s use the columns property delete.
Code:
Private Sub dele() Columns(6).delete End Sub
Within the columns(), ‘6 ‘is mentioned since the specified column is the 6th column in the table.
Step 3: Run this code by hitting the F5 or Run button and see the output.
If you check the table below, you will see that the column “Friday” is got deleted.
VBA Delete Column – Example #2
Another easy method to perform the deletion is specifying the column reference by alphabetical order.
Follow the below steps to delete a column in Excel using VBA.
Step 1: Since Friday is column F, mention the column address below in the code.
Code:
Private Sub dele1() Columns("F").delete End Sub
While using the alphabet, remember to put the alphabet in the double quotation.
Step 2: Run this code by hitting the F5 or Run button and see the output.
This will delete column Friday from the table.
VBA Delete Column – Example #3
The column range should be specified in the same table if you want to delete more than one column.
Follow the below steps to delete the last two columns in Excel.
Step 1: The code can be modified as below.
Code:
Private Sub dele2() Columns("E:F").delete End Sub
The range is specified as “E:F,” which will delete the column from F to G.
Step 2: Run this code by hitting the F5 or Run button and see the output.
The last two columns are deleted.
VBA Delete Column – Example #4
Let’s see what will happen if we delete the middle columns in a table.
Follow the below steps to delete the middle columns in a table.
Step 1: The code can be modified as below.
Code:
Private Sub dele3() Columns("B:C").delete End Sub
Here “B:C” refers to the column Monday and Tuesday.
Step 2: Run this code by hitting the F5 or Run button and see the output.
After running the code, if you check the table, you can see the column after “B:C” is shifted from right to left.
VBA Delete Column – Example #5
In the above example, we did not mention a worksheet name; if the workbook contains more than one sheet, then the worksheet name should be specified, then only the code will perform the proper action.
The two sheets contain the details of employees for two months, Jan and Feb. Since the same worksheet have more than one sheet, to avoid errors better to specify the sheet name.
Follow the below steps to delete a column in Excel using VBA.
Step 1: You should select the sheet using the code.
Code:
Private Sub dele4() Worksheets("Jan").Select End Sub
Step 2: The code for deleting the column should be given.
Code:
Private Sub dele() Worksheets("Jan").Select Columns("B:C").delete End Sub
Step 3: Run this code by hitting the F5 or Run button and see the output.
I will select the ‘Jan’ sheet and delete columns B and C, which represent Monday and Tuesday, from the table.
Deleting multiple columns using range object in VBA
The range object also deletes a column in VBA instead of column delete. If you use the range object, the code will look as follows:
Private Sub dele() Range("B:C").Delete End Sub
The range (“B:C”) represents the column range that needs to be deleted.
Deleting a single column using the range object
To delete a single column using the range object, the range must be specified below.
Private Sub dele() Range("B:B").Delete End Sub
Range (“B: B”) points to the single column, and it will be deleted.
Things to Remember
- The column can be specified using the column number or corresponding alphabet while deleting.
- You cannot use numbers as column references when deleting multiple columns.
- Instead of the columns property, the Range object can delete a column in VBA.
Recommended Articles
This has been a guide to VBA Delete Column. Here we discussed how to Delete Columns in Excel using VBA, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
