如何选择清除表内容而不破坏表？ [英] How to select clear table contents without destroying the table?

问题描述

我在2010年的excel中有一个vba功能，我使用这里的人的帮助来构建。此功能复制表/表单的内容，对它们进行排序，并将它们发送到相应的表。

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

有没有办法呢？

解决方案

如何进行：

  ACell.ListObject.DataBodyRange.Rows.Delete 
  

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

编辑：我要修改一个部分我的答案来自您之前的帖子，因为它主要是你想要的。这只留下一行：

 使用loSource 
 .Range.AutoFilter 
 .DataBodyRange.Offset（1 ）.Resize（.DataBodyRange.Rows.Count -1）.Rows.Delete 
 .DataBodyRange.Rows（1）。 $ b  

如果你想让所有的行完整的公式和whatnot，只要做：

 使用loSource 
 .Range.AutoFilter 
 .DataBodyRange.Specialcells（xlCellTypeConstants）.ClearContents 
结束
  

哪些是接近@Readify建议的，除了它不会清除公式。

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.

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.

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

解决方案

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.

这篇关于如何选择清除表内容而不破坏表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！