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.
- Overview on VBA Delete Row from Table in Excel
- Syntax to Delete Row from Table using VBA in Excel
- Example to Delete the First Row from Table on the Worksheet
- Remove the Fourth Row from Table on the Worksheet in Excel
- Delete Multiple Rows from Table in Excel using VBA
- Instructions to use VBA Macro code
- Other Related References
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.
Hi, How do you delete the last row in the column only?
Hi,
Can this be changed to delete the last row from the table?
If I change the line
loTable.ListRows.Add
to
loTable.ListRows.Delete
It returns a runtime error.
‘VBA Add New Row to Table
Sub VBAF1_Add_Row_to_Table()
‘Declare Variables
Dim oSheetName As Worksheet
Dim sTableName As String
Dim loTable As ListObject
‘Define Variable
sTableName = “Timesheet_Table”
‘Define WorkSheet object
Set oSheetName = Sheets(“Timesheet”)
‘Define Table Object
Set loTable = oSheetName.ListObjects(sTableName)
‘Add New row to the table
loTable.ListRows.Add
End Sub