VBA Delete Row from Table in Excel

VBA Delete Row from Table in Excel. We can Delete or remove a single Row or multiple Rows from Table at any position. Default first Rows 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 Row from Table using VBA in Excel

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

ListRows(Number).Delete

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

Example to Delete the First Row from Table on the Worksheet

Let us see the example to Delete Row 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 Row from the Table
Sub VBAF1_Delete_First_Row_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 Row from the table
    loTable.ListRows(1).Delete
    
    
End Sub

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

Remove the Fourth Row from Table on the Worksheet

Let us see the example to remove the fourth Row from the table on the worksheet. The difference between above and below procedure is change in Row 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 Rows from Table in Excel using VBA

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

  'VBA Delete Multiple Rows from the Table
Sub VBAF1_Delete_Multiple_Rows_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 Rows from the table
        loTable.ListRows(1).Delete
    Next
    
End Sub

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

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