vba 从 Excel 表中删除所有数据行（除了第一个）

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

提问by 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.Counton a table with no rows (just header and/or footer) causes errors.

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

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.

我四处寻找解决方案但找不到完整的解决方案，所以我希望我对这个问题的回答将来对其他人有用。

回答by royUK

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

回答by 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

附带说明一下，如果我需要通知用户第一行或其他行是否被删除，我会添加适当的错误处理

回答by Erik

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

我有 3 个可以正常工作的例程，只需在表中选择一个单元格并运行其中一个子例程

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

回答by Hyman McCoy

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.

这对你有用吗？我已经在 Excel 2010 中对其进行了测试，并且运行良好。这是使用名为“Table1”的表，该表使用 A 到 G 列。

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

回答by HarveyFrench

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

（PS别问我为什么！）

回答by Max Makhrov

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

回答by Tariq Khalaf

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

上面的代码在 Excel 2010 中不起作用我下面的代码允许您查看所需的工作表数量，然后选择表格并删除行

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

回答by David Gard

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

这VBA Sub将删除所有数据行（除了第一个，它只会清除）-

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