Delete all data rows from an Excel table (apart from the first)

Question

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.

If you don't want to use exceptions for handling code flow, I would guess that you're running into errors because you have a null reference exception at some point in that call (if Table, DataBodyRange, or Rows is null you will have an error). You could fix this by error checking before making the call. — Seth Moore, Dec 18 '13 at 17:06

score 39 · Answer 1 · edited Jul 02 '16 at 19:32

39

This is how I clear the data:

Sub Macro3()
    With Sheet1.ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
End Sub

edited Jul 02 '16 at 19:32

oldtechaa

1,463
1
14
26

answered Jul 02 '16 at 16:39

royUK

391
3
2

6

Most of the answers here assume your table is on the current sheet or you know the sheet name. To clear a named table without referencing the sheet, I changed your `With Sheet1.ListObjects("Table1")` line to this `With Range("Table1").ListObject` – Ben Jul 26 '18 at 14:16
Perhaps Application.Range would be more thorough yet at proper choice if I understand it right? – JeopardyTempest Aug 21 '18 at 10:20

score 24 · Accepted Answer · edited Dec 18 '13 at 16:55

24

Your code can be narrowed down to

Sub DeleteTableRows(ByRef Table As ListObject)
    On Error Resume Next
    '~~> Clear Header Row `IF` it exists
    Table.DataBodyRange.Rows(1).ClearContents
    '~~> Delete all the other rows `IF `they exist
    Table.DataBodyRange.Offset(1, 0).Resize(Table.DataBodyRange.Rows.Count - 1, _
    Table.DataBodyRange.Columns.Count).Rows.Delete
    On Error GoTo 0
End Sub

Edit:

On a side note, I would add proper error handling if I need to intimate the user whether the first row or the other rows were deleted or not

edited Dec 18 '13 at 16:55

David Gard

11,225
36
115
227

answered Dec 18 '13 at 16:42

Siddharth Rout

147,039
17
206
250

True, I suppose I don't specifically need to reset the error when I do. Will the `End Sub` reset the error though, or will I still need to do that? – David Gard Dec 18 '13 at 16:45
No need for the user to know about whether or not the rows have been previously delete, it's just to ensure any old data is cleared. – David Gard Dec 18 '13 at 16:53

score 11 · Answer 3 · answered Jan 10 '17 at 12:41

I have 3 routines which work just fine, just select a cell in a table and run one of the subroutines

Sub ClearTable()
If Not ActiveCell.ListObject Is Nothing Then
    ActiveCell.ListObject.DataBodyRange.Rows.ClearContents
End If
End Sub

and Shrink Table to remove the databody range except from the headers and the first data row

Sub ShrinkTable()
If Not ActiveCell.ListObject Is Nothing Then
    ActiveCell.ListObject.DataBodyRange.Delete
End If
End Sub

and Delete Table to completely delete the table from the sheet

Sub DeleteTable()
If Not ActiveCell.ListObject Is Nothing Then
    ActiveCell.ListObject.Delete
End If
End Sub

Well done for distinguishing clear, shrink, and delete. – Tulio Casagrande Jun 29 '20 at 13:13 — Tulio Casagrande, Jun 29 '20 at 13:13

score 4 · Answer 4 · edited Jan 02 '15 at 22:04

I wanted to keep the formulas in place, which the above code did not do.

Here's what I've been doing, note that this leaves one empty row in the table.

Sub DeleteTableRows(ByRef Table As ListObject, KeepFormulas as boolean)

On Error Resume Next

if not KeepFormulas then
    Table.DataBodyRange.clearcontents
end if

Table.DataBodyRange.Rows.Delete

On Error GoTo 0

End Sub

(PS don't ask me why!)

score 3 · Answer 5 · answered Aug 02 '20 at 16:34

3

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).

answered Aug 02 '20 at 16:34

Dom

31
3

score 2 · Answer 6 · answered Nov 17 '14 at 19:08

Would this work for you? I've tested it in Excel 2010 and it works fine. This is working with a table called "Table1" that uses columns A through G.

Sub Clear_Table()
    Range("Table1").Select
    Application.DisplayAlerts = False
    Selection.Delete
    Application.DisplayAlerts = True
    Range("A1:G1").Select
    Selection.ClearContents
End Sub

score 0 · Answer 7 · edited Dec 18 '13 at 16:44

0

This VBA Sub will delete all data rows (apart from the first, which it will just clear) -

Sub DeleteTableRows(ByRef Table as ListObject)

        '** Work out the current number of rows in the table
        On Error Resume Next                    ' If there are no rows, then counting them will cause an error
        Dim Rows As Integer
        Rows = Table.DataBodyRange.Rows.Count   ' Cound the number of rows in the table
        If Err.Number <> 0 Then                 ' Check to see if there has been an error
            Rows = 0                            ' Set rows to 0, as the table is empty
            Err.Clear                           ' Clear the error
        End If
        On Error GoTo 0                         ' Reset the error handling

        '** Empty the table *'
        With Table
            If Rows > 0 Then ' Clear the first row
                .DataBodyRange.Rows(1).ClearContents
            End If
            If Rows > 1 Then ' Delete all the other rows
                .DataBodyRange.Offset(1, 0).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
            End If
        End With

End Sub

edited Dec 18 '13 at 16:44

Siddharth Rout

147,039
17
206
250

answered Dec 18 '13 at 16:33

David Gard

11,225
36
115
227

1

If you would have asked this question, we would have given you a similar answer :) However you don't need 2 IF conditions. You can combine them. – Siddharth Rout Dec 18 '13 at 16:37
2

I agree with @SiddharthRout Also you have a typo `Err.clesr` should be `Err.Clear` – engineersmnky Dec 18 '13 at 16:38
@engineersmnky - Thanks, I just spotted that in VBA! – David Gard Dec 18 '13 at 16:46
@SiddharthRout - In this context I do need 2x `If`, as they both check different things. However, I'm looking at another answer that would avoid the need for an `If` statement all together. Thanks. – David Gard Dec 18 '13 at 16:47

score 0 · Answer 8 · answered Sep 24 '15 at 13:46

I suggest first clearcontents, then resize Table:

Sub DeleteTableRows(ByRef Table As ListObject)

     Dim R               As Range

On Error Resume Next

    Table.DataBodyRange.ClearContents
    Set R = Table.Range.Rows(1).Resize(2)
    Table.Resize R

On Error GoTo 0

End Sub

score 0 · Answer 9 · answered Sep 20 '17 at 08:25

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

score 0 · Answer 10 · answered Sep 24 '22 at 10:02

0

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.

answered Sep 24 '22 at 10:02

milo5m

619
1
3
8

Delete all data rows from an Excel table (apart from the first)

10 Answers10

Linked

Related