VBA编码可识别和清除特定表中的数据 [英] VBA coding to identify and clear data in a specific table

问题描述

我对使用VBA并不陌生，我想向按钮添加代码，该代码将识别称为"QA_Activity"的表的大小，并在单击按钮时清除标题行以外的所有内容.我希望这将非常简单，但是在如何确定每个月可能有不同大小的表方面却很挣扎.预先非常感谢.

I am new to using VBA and would like to add coding to a button that will identify the size of a table called "QA_Activity" and clear all but the header row on clicking the button. I was hoping that this would be quite simple but am struggling as to how to identify a table that could be a different size each month. Many thanks in advance.

推荐答案

表在VBA中称为ListObjects.ListObject具有一个名为.DataBodyRange的属性，该属性包含标题下的所有内容.那有一个.clear方法.

Tables are called ListObjects in VBA. A ListObject has a property called a .DataBodyRange that contains everything under the header. That has a .Clear method.

我通常使用以下语法清除ListObject的主体:

I generally use this syntax to clear the body of a ListObject:

Range("Table1").ListObject.DataBodyRange.Clear

Range("Table1")位允许我找到ListObject，即使我不知道它在哪张纸上.之所以可行，是因为表也是 命名范围，您可以按名称进行寻址.因此，您只需说转到名为Table1的命名范围，并获取存在于其中的ListObject"即可.否则，您需要事先知道ListObject位于哪张纸上，并使用以下内容:

The Range("Table1") bit allows me to find the ListObject even if I don't know what sheet it's on. This works because Tables are also Named Ranges that you can address by name. So you just say "Go to the Named Range called Table1, and get me the ListObject that lives there". Otherwise you would need to know in advance what sheet the ListObject is on, and use the following:

Worksheets("SomeSheet").Listobjects("Table1").DataBodyRange.Clear

...工作正常，直到有一天将表移到另一张工作表，而忘记更新代码.

...which works just fine until one day you move the Table to a different sheet, and forget to update your code.

请注意，表不能保证实际上具有.DataBodyRange，因为有人可能已经删除了标题下的所有行.例如，使用此表:

Note that a table is not guaranteed to actually have a .DataBodyRange, because someone may have deleted all the rows under the header. For instance, take this Table:

DataBodyRange中有多少行?

How many rows does it have in the DataBodyRange?

? Range("Table1").ListObject.DataBodyRange.Rows.Count
 3

好的，现在我要删除这些行:

Okay, now I'm going to delete those rows:

...离开这个:

该DataBodyRange中现在有几行?

How many rows in that DataBodyRange now?

? Range("Table1").ListObject.DataBodyRange.Rows.Count

糟糕...如果不存在.DataBodyRange，则无法引用.

Whoops...you can't reference a .DataBodyRange if it don't exist.

为了安全起见，在尝试引用.DataBodyRange之前，先粘贴 On Error Resume Next ，然后再引用 On Error Goto 0 .或其他更奇特的东西.

So to be safe, stick an On Error Resume Next before you try to reference a .DataBodyRange, and an On Error Goto 0 afterwards. Or something fancier.

这篇关于VBA编码可识别和清除特定表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！