vba 如何在不破坏表格的情况下选择清除表格内容？

How to select clear table contents without destroying the table?

提问by SpeedCrazy

I have a vba function in excel 2010 that I built using help from people on here. This function copies the contents of a table/form, sorts them, and sends them to the appropriate tables.

我在 excel 2010 中有一个 vba 函数，我使用这里的人的帮助构建了它。此函数复制表/表单的内容，对它们进行排序，然后将它们发送到适当的表。

Now after running this function I want the original table to be cleared. I can achieve this with the following code, assuming ACell has been defined as the first cell in the table. ACell.ListObject.Range.ClearContentsworks fine, the only problem is it deletes the table as well as the data values.

现在运行此函数后，我希望清除原始表。我可以使用以下代码实现这一点，假设 ACell 已被定义为表中的第一个单元格。 ACell.ListObject.Range.ClearContents工作正常，唯一的问题是它删除了表以及数据值。

Is there any way around this? I would rather not have to set the table up every time I enter some data.

有没有办法解决？我宁愿不必每次输入一些数据时都设置表格。

回答by Doug Glancy

How about:

怎么样：

ACell.ListObject.DataBodyRange.Rows.Delete

That will keep your table structure and headings, but clear all the data and rows.

这将保留您的表结构和标题，但清除所有数据和行。

EDIT: I'm going to just modify a section of my answer from your previous post, as it does mostly what you want. This leaves just one row:

编辑：我将只修改您上一篇文章中的一部分答案，因为它主要满足您的需求。这仅留下一行：

With loSource
   .Range.AutoFilter
   .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
   .DataBodyRange.Rows(1).Specialcells(xlCellTypeConstants).ClearContents
End With

If you want to leave all the rows intact with their formulas and whatnot, just do:

如果您想保留所有行及其公式等内容，只需执行以下操作：

With loSource
   .Range.AutoFilter
   .DataBodyRange.Specialcells(xlCellTypeConstants).ClearContents
End With

Which is close to what @Readify suggested, except it won't clear formulas.

这与@Readify 建议的很接近，只是它不会清除公式。

回答by Reafidy

Try just clearing the data (not the entire table including headers):

尝试只清除数据（不是整个表，包括标题）：

ACell.ListObject.DataBodyRange.ClearContents

回答by Patrick Honorez

I reworked Doug Glancy's solution to avoid rows deletion, which can lead to #Ref issue in formulae.

我重新设计了 Doug Glancy 的解决方案以避免行删除，这会导致公式中的 #Ref 问题。

Sub ListReset(lst As ListObject)
'clears a listObject while leaving row 1 empty, with formulae
    With lst
        If .ShowAutoFilter Then .AutoFilter.ShowAllData
        On Error Resume Next
        With .DataBodyRange
            .Offset(1).Rows.Clear
            .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
        End With
        On Error GoTo 0
        .Resize .Range.Rows("1:2")
    End With
End Sub

回答by Ben

There is a condition that most of these solutions do not address. I revised Patrick Honorez's solution to handle it. I felt I had to share this because I was pulling my hair out when the original function was occasionally clearing more data that I expected.

大多数这些解决方案都没有解决一个条件。我修改了 Patrick Honorez 的解决方案来处理它。我觉得我必须分享这个，因为当原始功能偶尔会清除我预期的更多数据时，我正在拔头发。

The situation happens when the table only has one columnand the .SpecialCells(xlCellTypeConstants).ClearContentsattempts to clear the contents of the top row. In this situation, only one cell is selected (the top row of the table that only has one column) and the SpecialCells command applies to the entire sheet instead of the selected range. What was happening to me was other cells on the sheet that were outside of my table were also getting cleared.

当表只有一列并且.SpecialCells(xlCellTypeConstants).ClearContents试图清除顶行的内容时，就会发生这种情况。在这种情况下，只选择了一个单元格（表格的顶行，只有一列）并且 SpecialCells 命令应用于整个工作表而不是所选范围。发生在我身上的是我桌子外面的工作表上的其他单元格也被清除了。

I did some digging and found this advice from Mathieu Guindon: Range SpecialCells ClearContents clears whole sheet

我做了一些挖掘，发现了 Mathieu Guindon 的这个建议： Range SpecialCells ClearContents clears entire sheet

Range({any single cell}).SpecialCells({whatever}) seems to work off the entire sheet.

Range({more than one cell}).SpecialCells({whatever}) seems to work off the specified cells.

Range({any single cell}).SpecialCells({whatever}) 似乎适用于整个工作表。

Range({more than one cell}).SpecialCells({whatever}) 似乎可以处理指定的单元格。

If the list/table only has one column (in row 1), this revision will check to see if the cell has a formula and if not, it will only clear the contents of that one cell.

如果列表/表格只有一列（在第 1 行），此修订版将检查单元格是否有公式，如果没有，它只会清除该单元格的内容。

Public Sub ClearList(lst As ListObject)
'Clears a listObject while leaving 1 empty row + formula
' https://stackoverflow.com/a/53856079/1898524
'
'With special help from this post to handle a single column table.
'   Range({any single cell}).SpecialCells({whatever}) seems to work off the entire sheet.
'   Range({more than one cell}).SpecialCells({whatever}) seems to work off the specified cells.
' https://stackoverflow.com/questions/40537537/range-specialcells-clearcontents-clears-whole-sheet-instead

    On Error Resume Next

    With lst
        '.Range.Worksheet.Activate ' Enable this if you are debugging 

        If .ShowAutoFilter Then .AutoFilter.ShowAllData
        If .DataBodyRange.Rows.Count = 1 Then Exit Sub ' Table is already clear
        .DataBodyRange.Offset(1).Rows.Clear

        If .DataBodyRange.Columns.Count > 1 Then ' Check to see if SpecialCells is going to evaluate just one cell.
            .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
        ElseIf Not .Range.HasFormula Then
            ' Only one cell in range and it does not contain a formula.
            .DataBodyRange.Rows(1).ClearContents
        End If

        .Resize .Range.Rows("1:2")

        .HeaderRowRange.Offset(1).Select

        ' Reset used range on the sheet
        Dim X
        X = .Range.Worksheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73

    End With

End Sub

A final step I included is a tip that is attributed to John Walkenbach, sometimes noted as J-Walkenbach tip 73Automatically Resetting The Last Cell

我包括的最后一步是约翰沃肯巴赫的提示，有时被称为J-Walkenbach tip 73自动重置最后一个单元格

回答by Brewer

I use this code to remove my data but leave the formulas in the top row. It also removes all rows except for the top row and scrolls the page up to the top.

我使用此代码删除我的数据，但将公式保留在顶行。它还会删除除顶行之外的所有行并将页面向上滚动到顶部。

Sub CleanTheTable()
    Application.ScreenUpdating = False
    Sheets("Data").Select
    ActiveSheet.ListObjects("TestTable").HeaderRowRange.Select
    'Remove the filters if one exists.
    If ActiveSheet.FilterMode Then
    Selection.AutoFilter
    End If
    'Clear all lines but the first one in the table leaving formulas for the next go round.
    With Worksheets("Data").ListObjects("TestTable")
    .Range.AutoFilter
    On Error Resume Next
    .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
    .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    ActiveWindow.SmallScroll Down:=-10000

    End With
Application.ScreenUpdating = True
End Sub