VBA Delete Column from Table

VBA Delete Column from Table in Excel

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 Use Example Macro Code

You can follow step by step instructions to run above macro example VBA code.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window in the visual basic editor(VBE)
  • Run macro by clicking F5 or Run command
  • The difference between before and after macro, see the output screenshot.

Other Related References

Click on the following links for related reference articles. It helps for your reference.

VBA Tables Tables Examples VBA Functions List

Leave a Comment

Your email address will not be published. Required fields are marked *