Just recently I've been trying to delete all data rows in a table, apart from the first (which needs to just be cleared)
Some of the tables being actioned could already have no rows, so I was running it to problems as using .DataBodyRange.Rows.Count on a table with no rows (just header and/or footer) causes errors.
I looked all over for a solution an could not find a whole one, so I hope my answer to this question will be useful to others in the future.
I'm simply using this:
On Error Resume Next
Worksheets("Sheet1").ListObjects("Table1").DataBodyRange.Rows.Delete
The first line stays in all cases (it is cleared, of course).
The codes above wouldn't work in Excel 2010 My code bellow allows you to go through number of sheets you would like then select tables and delete rows
Sub DeleteTableRows()
Dim table As ListObject
Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject
'select ammount of sheets want to this to run
For i = 1 To 3
Sheets(i).Select
Range("A1").Select
Set SelectedCell = ActiveCell
Selection.AutoFilter
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Clear first Row
ActiveTable.DataBodyRange.Rows(1).ClearContents
'Delete all the other rows `IF `they exist
On Error Resume Next
ActiveTable.DataBodyRange.Offset(1, 0).Resize(ActiveTable.DataBodyRange.Rows.Count - 1, _
ActiveTable.DataBodyRange.Columns.Count).Rows.Delete
Selection.AutoFilter
On Error GoTo 0
Next i
Exit Sub
'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
End Sub
If you already know the table name in advance, this is a short approach I'd use
With [TableName].ListObject
If Not .DataBodyRange Is Nothing Then: .DataBodyRange.Delete
End With
No need for sheet reference etc.