Excel macro to delete all rows in a table except the first one

Every now and then, I need an Excel macro to be able to delete all the rows in a table, except the first one. The following macro will do just that:

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

This is not something that I came up with, but a piece of code that I found on StackOverflow.

Leave a Reply

Your email address will not be published.