VBA to Delete Column in Excel (9 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you how to delete the column in Excel with VBA.


Generic Syntax

The generic syntax to delete single or multiple columns in Excel using VBA is:

Columns(column number/ column address).Delete

VBA to Delete Column in Excel: 9 Criteria

In this section, you will learn how to delete a single column, multiple adjacents and non-adjacent columns, blank columns, columns with blank cells, columns from another worksheet, columns from a table, and many more criteria with VBA in Excel.

We will use the following dataset as an example.


1. VBA to Delete Single Column in Excel

Steps to delete a single column in Excel with VBA are given below.

Steps:

  • Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • From the menu bar, click Insert -> Module.

  • Copy the following code and paste it into the code window.
Sub DeleteCol()
Columns(5).Delete
End Sub

Your code is now ready to run.

VBA to Delete Single Column in Excel

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Column 5 (Apr) is deleted from the dataset.

  • You can delete a column based on the column address instead of the column number. Insert the column address instead of the column number inside the parentheses. Then the code will look like this,
Sub DeleteCol()
Columns(E).Delete
End Sub

This piece of code will delete the column E (Apr) from the dataset.


2. VBA to Remove Multiple Columns in Excel

Steps to remove multiple columns in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy the following code and paste it into the code window.
Sub DeleteMCol()
Columns(“C:E”).Delete ‘//delete cells from range C to E
End Sub

VBA to Delete Multiple Column in Excel

  • Run the macro.

All the previous columns from C to E (Feb to Apr) have been deleted.

  • This process can also be done with the range. The code to delete multiple columns using range is shown below:
Sub DeleteMCol()
Range(“C:E”).Delete
End Sub

This will also delete columns from C to E (Feb to Apr).

Read more: VBA Macro to Delete Columns Based on Criteria in Excel


3. VBA to Remove Multiple Non-Adjacent Columns in Excel

Steps to remove multiple non-adjacent columns in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy and paste the following code.
Sub DeleteNAdjCol()
Range("C:E, G:I").Delete
End Sub

VBA to Delete Non-Adjacent Multiple Columns in Excel

  • Run the macro.

All the previous columns from C to E (Feb to Apr) and G to I (Jun to Aug) have been deleted.


4. VBA to Delete Columns with Blank Cells in Excel

Steps to delete columns with blank cells in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy and paste the following code.
Sub DeleteBlankCells()
Range("A1:J10").Select '//range of the dataset
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireColumn.Delete
End Sub

VBA to Delete Columns with Blank Cells in Excel

  • Run the macro.

All the columns carrying blank cells are deleted now.


5. VBA to Delete Blank Columns in Excel

Look at the following dataset consisting of blank columns. We will see how to delete the blank columns in Excel with VBA macro.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy the following code and paste it.
Sub DeleteBlankCol()
Dim i As Integer
For i = 1 To 4
Columns(i + 1).Delete
Next i
End Sub

VBA to Delete Blank Columns in Excel

  • Run the macro.

All the blank columns are deleted.


6. VBA to Delete Column from a Specific Worksheet in Excel

Steps to delete the column from a specific worksheet in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy and paste the following code.
Sub DeleteColFromSheet()
Worksheets("New Column").Select '// set the sheet name
Columns("D:F").Delete
End Sub

VBA to Delete Column from a Specific Worksheet

  • Run the macro.

All the previous columns from D to F (Mar to May) from the New Column worksheet have been deleted.

Read More: How to Delete Columns with Specific Text in Excel


7. VBA to Remove Column from a Table in Excel

Steps to delete the column from a table in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy the below code and paste it.
Sub DeleteColTable()
Dim srcTable As ListObject
Dim srcSheet As Worksheet
'Set the table from which column is to be deleted
Set srcTable = ActiveSheet.ListObjects("ExTable")
'Set the sheet that contains the table
Set srcSheet = Sheet8
With srcSheet
srcTable.ListColumns(5).Delete
End With
End Sub

VBA to delete Column from a Table in Excel

  • Run the macro.

Column 5 (Apr) is deleted from the table.


8. VBA to Remove Multiple Columns from a Table in Excel

Steps to delete multiple columns from a table in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy the following code and paste it.
Sub DeleteMColTable()
Dim srcTable As ListObject
Dim srcSheet As Worksheet
Dim i As Integer
'Set the table from which column is to be deleted
Set srcTable = ActiveSheet.ListObjects("ExTable2")
'Set the sheet that contains the table
Set srcSheet = Sheet9
'Run the loop twice as we need to delete 2 columns
For i = 1 To 2
With Source
srcTable.ListColumns(5).Delete
End With
Next i
End Sub

VBA to Remove Multiple Columns from a Table in Excel

  • Run the macro.

Columns 5 and 6 (Apr and May) have been deleted from the table.


9. VBA to Delete Column from a Table Based on Column Header in Excel

Steps to delete the column from a table based on the column header in Excel with VBA are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Copy and paste the following code.
Sub DeleteColHeaderTable()
Dim srcTable As ListObject
Dim srcSheet As Worksheet
Dim i As Integer
Dim colName As String
'Set the table from which column is to be deleted
Set srcTable = ActiveSheet.ListObjects("ExTable3")
'Set the sheet that contains the table
Set srcSheet = Sheet10
'Case sensitive
colName = "Apr"
'Loop through all the columns in the table
For i = 1 To srcTable.ListColumns.Count
With srcSheet
'Check the column header
If srcTable.ListColumns(i).Name = colName Then
srcTable.ListColumns(i).Delete
Exit For
End If
End With
Next i
End Sub

VBA to Delete Column from a Table Based on Column Header in Excel

  • Run the macro.

The column header named Apr is deleted from the table.

Read More: How to Delete Columns Based on Header Using VBA in Excel


Download Workbook

You can download the free practice Excel workbook from here.


Conclusion

This article showed you how to delete the column in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo