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

问题描述

最近，我一直在尝试删除表中的所有数据行，除了第一个(需要清除的行)之外

Just recently I've been trying to delete all data rows in a table, apart from the first (which needs to just be cleared)

正在处理的某些表可能已经没有行，所以我遇到了问题，因为在没有行的表(仅页眉和/或页脚)上使用.DataBodyRange.Rows.Count会导致错误.

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

修改:

另一方面，如果我需要通知用户第一行还是其他行是否已删除，我将添加适当的错误处理

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

这篇关于从Excel表中删除所有数据行(第一个表除外)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！