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

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.ClearContents works 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.

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

3 个解决方案

#1

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所建议的，除了它不会清除公式。

#2

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

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

ACell.ListObject.DataBodyRange.ClearContents

#3

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

#1