从Excel表中删除所有数据行（除第一个外）(Delete all data rows from an Excel table (apart from the first))

从Excel表中删除所有数据行（除第一个外）(Delete all data rows from an Excel table (apart from the first))

就在最近，我一直试图删除表中的所有数据行，除了第一个（只需要清除）

一些被执行的表可能已经没有行了，所以我在运行时遇到了问题，因为在没有行的表（只是标题和/或页脚）上使用.DataBodyRange.Rows.Count会导致错误。

我查找了一个解决方案，找不到一个完整的解决方案，所以我希望我对这个问题的回答将在未来对其他人有用。

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.

最满意答案

你的代码可以缩小到

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

编辑 ：

在附注中，如果我需要密切关注第一行或其他行是否被删除，我会添加适当的错误处理

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