Clear columns in excel vba

Normally in an Excel worksheet, we have two different methods to delete columns: the keyboard shortcut and the right-click and insert method. But, in VBA, we must use the “Delete” command and the entire column statement to delete any column together. If we need to delete a single column, we give a single column reference, but we give multiple column references for multiple columns.

We perform many actions in Excel like cutting, copying, pasting, adding, deleting, and inserting regularly. We can use all of these actions using VBA coding. However, one of the important concepts we need to learn in VBA is the “deleting column.” This article 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 must first identify which column to delete. The selection of deleted columns differs from one scenario to another, so that 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 VBA’s syntax of the “Delete Column” method is below.

Columns (Column Reference).Delete

So, we can construct the code like this:

Columns (2).Delete or Columns (“B”).Delete

It will delete column number 2, i.e., column B.

If we want to delete multiple columns, we cannot enter columns. Instead, we need to reference the columns by column headers, i.e., alphabets.

Columns (“A:D”).Delete

It 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 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 examples of deleting columns using VBA.

Example #1 – Using Delete Method

Assume you have the datasheet, something like the below.

If we want to delete the month “Mar,” first select the column property.

Code:

Sub Delete_Example1()

  Columns(

End Sub

Mention the column number or alphabet. In this case, it is either 3 or C.

Code:

Sub Delete_Example1()

  Columns(3).

End Sub

Use the Delete method.

Note: You would not get the IntelliSense list to select the Delete method. Just type “Delete.”

Code:

Sub Delete_Example1()

  Columns(3).Delete

End Sub

Or you can enter the column address like this.

Code:

Sub Delete_Example1()

  Columns("C").Delete

End Sub

Run this code using the F5 key, or you can run it 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 columns 2 to 4, we can pass the code like the below.

Code:

Sub Delete_Example1()

  Columns("C:D").Delete

End Sub

Run this code manually through the run option or press the F5 key. It 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 a good practice to delete columns. Deleting the column without referring to the worksheet name is dangerous.

If you have not mentioned the worksheet name, then whichever sheet is active will delete columns of that sheet.

First, we need to select the worksheet by its name.

Code:

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 objectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.

Code:

Sub Delete_Example2()

   Worksheets("Sales Sheet").Select
   Range("B:D").Delete

End Sub

It will delete columns B to D of the worksheet “Sales Sheet.” For this code, it does not matter which is active. Still, it will delete the mentioned columns of that sheet only.

We can construct the VBA 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.read more in the single line itself.

Code:

Sub Delete_Example2()

    Worksheets("Sales Sheet").Range("B:D").Delete

End Sub

It 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 alternate column. Then, we can use the below code.

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.

Note: This works only for alternative blank columns.

Example #4 – Delete Blank Cells Columns

Now, look at this example. In certain situations, we need to delete the entire column if we find any blank cells in the data range. Consider the below data for an example.

All the yellow-colored cells are blank. So here, we require to delete all the blank cell columns. The below code will do that.

Code:

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 from A1 to F9, so first, we must select that range. The below code will do that.

Range("A1:F9").Select

We need to select the blank cells in this selected range of cells. So, to select a blank cell, we need a special cell property. In that property, we have used cell type as blank.

Selection.SpecialCells(xlCellTypeBlanks).Select

Next, it will select all the blank cells, and we are deleting the entire selection column in the selection.

Selection.EntireColumn.Delete

So, our result will look like this.

Wherever it has found the blank cell, it has deleted those blank cells entirely.

You can download this Excel VBA Delete Column here – VBA Delete Column Template

Recommended Articles

This article has been a guide to VBA Delete Column. Here, we learn four methods to delete columns using Excel VBA code, practical examples, and downloadable codes. Below are some useful Excel articles related to VBA: –

• How to Delete File in VBA?
• VBA Integer Function
• IsEmpty Function in VBA
• IFERROR in VBA