VBA Delete Column

VBA Delete Column

Copy, paste, cut, delete, insert is some of the common operations used to perform in excel. The easy method to do this is, using the shortcut keys or built-in functions. But when you want to perform the operation in a single click or automatically, VBA is the solution. 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 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 Column in Excel Using VBA?

We will learn how to Delete a Column in VBA with few examples in excel.

VBA Delete Column – Example #1

From an attendance database, the office leaving time for some employees are given. 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: So first we can create a simple function as dele() since the delete is a keyword which 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 a 6^th column in the table.

Step 3: Run this code by hitting F5 or Run button and see the output.

If you check the table, it will look as below. Where the column Friday got deleted.

VBA Delete Column – Example #2

Another easy method to perform the deletion is specifying the column reference by alphabetical order.

Follow below steps to delete a column in excel using VBA.

Step 1: Since Friday is column F, mention the column address as 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 F5 or Run button and see the output.

This will delete column Friday from the table.

VBA Delete Column – Example #3

In the same table if you want to delete more than one column then the column range should be specified.

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 specified as “E:F” and this will delete the column from F to G.

Step 2: Run this code by hitting 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 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: The sheet should be selected using the code.

Code:

Private Sub dele4()

Worksheets("Jan").Select

End Sub

Step 2: Now 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 F5 or Run button and see the output.

The sheet “Jan “will be selected and columns B, C i.e. Monday, Tuesday will be deleted from the table.

Deleting multiple columns using range object in VBA

The range object also used to delete a column in VBA instead of column delete. If the range object is used the code will look as below.

Private Sub dele()

Range("B:C").Delete

End Sub

The Range(“B:C”) is represented the column range which needs to be deleted.

Deleting a single column using the range object

To delete a single column using the range object the range need to be specified as 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 column number or corresponding alphabet while deleting.
• When deleting multiple columns, the numbers will not be accepted as a column reference.
• Instead of columns property, Range object can be used to delete a column in VBA.

Recommended Articles

This has been a guide to VBA Delete Column. Here we discussed how to Delete Column in Excel using VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Deleting a Row with VBA
2. Excel Move Columns
3. Transpose a Range in VBA
4. Excel Delete Row Shortcut