VBA Delete Column from Table in Excel

VBA Delete Column from Table

VBA Delete Column from Table in Excel. We can Delete or remove a single Column or multiple Columns from Table at any position. Default first Columns Deleted from the table. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. We have specified two examples in the following tutorial. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.

Syntax to Delete Column from Table using VBA in Excel

Here is the syntax to Delete Column from Table on the worksheet using VBA in Excel.

ListColumns(Number).Delete

Where Number contains the integer data type. It is a mandatory argument. Represents the column number to delete.

Example to Delete the First Column from Table on the Worksheet

Let us see the example to Delete column from Table on the worksheet. The sheet name defined as ‘Table‘. And we use table name as ‘MyTable1‘. You can change these two as per your requirement. We Delete method of the ListObject object.

 'VBA Delete the First Column from the Table
Sub VBAF1_Delete_First_Column_from_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
        
    'Delete a column from the table
    loTable.ListColumns(1).Delete
    
    
End Sub

Output: Here is the following output screenshot of above example macro VBA code.

VBA Delete Column from Table in Excel

Remove the Fourth Column from Table on the Worksheet

Let us see the example to remove the fourth column from the table on the worksheet. The difference between above and below procedure is change in column number.

'VBA Delete the Fourth Row from the Table
Sub VBAF1_Delete_Fourth_row_from_Table1()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Delete fourth Row from the table
    loTable.ListRows(4).Delete
    
End Sub

Delete Multiple Columns from Table in Excel using VBA

Here is another example to Delete multiple columns from the Table. In this example we delete three(3) columns from the table. You can specify the number of columns count in the for loop.

  'VBA Delete Multiple Columns from the Table
Sub VBAF1_Delete_Multiple_Columns_from_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyTable2"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
	'Loop through 3 times to delete
    For iCnt = 1 To 3
        'Delete multiple columns from the table
        loTable.ListColumns(1).Delete
    Next
    
End Sub

Output: Let us see the following output screenshot of above example macro VBA code.

VBA Delete Multiple Columns from Table

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment