Loop through Excel Tables selecting them, ready to have their contents removed

[Raddle]

Hi

Having created a load of test data I would now like to loop through all tables in the workbook (eventually, I want to create an array of the specific named tables, but let's walk first), deleting the data.

I can now loop through the tables, debug printing the names - tick
But I do not appear to be able to select a table cell, which then will allow me to run the delete routine.
Any help here gratefully received and I have resisted the temptation to come begging for two days .. yes I know.


Sub loopSheets()
Dim tbl As ListObject
Dim ws As Worksheet
Dim i As Integer




For Each ws In ActiveWorkbook.Worksheets ' use activeworkbook NOT this workbook for some reason
For Each tbl In ws.ListObjects
For i = 1 To 5 ' doubt there are more than 5 tables on a sheet although to have this 'open' would be great
Set tbl = ActiveSheet.ListObjects(i) ' I thought this was how to move through the tables per sheet
With tbl ' not sure if this is where it is breaking
ListObject.Range.Rows(2).Columns(1).Select


If Not ActiveCell.ListObject Is Nothing Then
ActiveCell.ListObject.DataBodyRange.Rows.ClearCont ents
End If
Debug.Print tbl.Name
End With


Debug.Print i
Next i
Next tbl

Debug.Print ws.Name
Next ws
End Sub

[Raddle]

I also tried this but I am not able to actually select the each table. This will work for only one table if I personally select a cell within that table.
If I select a cell outside of both table ranges, then neither table is cleared.

So I think the challenge remains, 'how does one select a cell in a table in' and then put that in a loop ... so grateful if anyone can help

Sub ForEachTables()
Dim tbl As ListObject
Dim contents As Variant

For Each tbl In ActiveSheet.ListObjects

Clearcontents

Next tbl

End Sub


Sub Clearcontents()


If Not ActiveCell.ListObject Is Nothing Then
ActiveCell.ListObject.DataBodyRange.Rows.Clearcont ents
End If
End Sub

[Minty]

Are you running this from Access or Excel?
And do you want to clear the entire worksheet, a column or a specific cell, delete the table or just clear out the table contents and leave it empty?

[Raddle]

I am running this all in excel

Need to just empty the body content of the tables

Thank you so much

The clear contents bit appears to work when I select a cell in the table myself but obvs I want it to loop through all tables itself.

Eventually I would like to give it an array of the table names I want to scratch and then have it just do those. But ... heheheeh

[Raddle]

Hi do I think this will do it ...

Sub Acnt_ClearOutTables()
Dim tbl As ListObject
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Sheets
ws.Activate ' this appeared to be the key bit and something that I have sturggled with using .. when it is needed and when not

For Each tbl In ActiveSheet.ListObjects
tbl.DataBodyRange.Rows.Clearcontents
Next tbl
Next ws
End Sub

Minty - thanks for coming back to me