VBA coding to identify and clear data in a specific table

Question

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.

Answer 1

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.

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

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

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.

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:

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:

...leaving this:

How many rows in that DataBodyRange now?

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

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

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.

Answer 2

First, create a named range. If required, you can make this dynamic. In this example the named range is Name "Data".

Then Trigger the Sub "Test" from the following VBA code.

Option Explicit

Sub ClearRange(ByVal rngCell As Range)

    Dim rngRange As Range

    Set rngRange = rngCell.CurrentRegion

    rngRange.Range(rngRange.Parent.Cells(2, 1), rngRange.Parent.Cells(rngRange.Rows.Count, rngRange.Columns.Count)).ClearContents

End Sub

Sub test()

    Dim rngCell As Range

    Set rngCell = ActiveSheet.Range("Data").Cells(1, 1)

    ClearRange rngCell

End Sub

This should clear the range except for the first row (headers).

Important: The header row and the first column of your range must be filled completely (no empty cells) for the above to work smoothly.