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

David Gard Published at Dev

David Gard

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.

Siddharth Rout

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-5

Comments

0 comments

From Dev

Related Related

Article

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

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

Get all rows apart from first and last

Delete all occurrences of a pattern apart from the first one

Printing out csv rows apart from first row

Delete rows from table

Delete all data from table but not the columns

Delete rows from a data table that exists in another data table

Delete rows from a data table that exists in another data table

Applying Application.ScreenUpdating to all worksheet apart from first row

Delete rows from a table without checking that they exist first

SQL - Delete table with 372 million rows starting from first row

Delete all but first 100 rows in table MYSQL

How to delete all rows from excel except header in c#

What is the best way in Android to delete all rows from a table

How to delete all rows from table which has no FK relation

What is the best way in Android to delete all rows from a table

Delete all rows from MySQL table using PHP

Delete rows from all tables

Delete X rows from table

Delete Rows of Data From Live Table inside Form?

How to delete all but first two rows in a table (headers is the first row)

PostgreSQL DELETE FROM (SELECT * FROM table FETCH FIRST 10 ROWS ONLY)

Cumulative average using data from multiple rows in an Excel table

Mysql - Delete All except 2 rows of all post_id1 from a table

Are all SSD the same apart from capacity?

Laravel migration failure apart from migration table

MySQL delete data from a table

Trigger to not delete Data from a table

Data wont delete from table

MySQL delete data from a table