Loop through all Excel tables in workbook

[Raddle]

Hi - trying to delete a bunch of test data from several tables in Excel.

I need to loop through each table and run the delete body content routine, which does work, but only if I manually select a cell in the table first ... so good but not ideal.

I 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

 

[NdNoviceHlp]

Hi Raddie and Welcome to the Board! Seems like you could just do this...

Sub ForEachTables()
Dim tbl As ListObject
For Each tbl In ActiveSheet.ListObjects
tbl.DataBodyRange.Rows.Clearcontents
Next tbl
End Sub

Also, using "Clearcontents" as a Sub or variable name is confusing for XL as Clearcontents is an XL term.
HTH. Dave
ps. please use code tags

 

[Raddle]

Dave

thank you soooo much

That does it for one sheet (the current one obvs) but I need it to run through the list of all tables in the workbook

 

[Raddle]

Noted about using confusing Excel terms and also the tagging code bit

(do you mean using the single quote and putting in pointer comments? )

 

[Raddle]

Also

Changing from activesheet to activeworkbook didn't fly .. not sure why

Sub ForRaddleEachTables()
Dim tbl As ListObject
For Each tbl In ActiveWorkbook.ListObjects
tbl.DataBodyRange.Rows.Clearcontents
Next tbl
End Sub

 

[Raddle]

Ah ha ... but this seems to work

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

For Each ws In ActiveWorkbook.Sheets
ws.Activate ' I had had loads and load and .. of trouble with this bit of code for years ... never seem to know when one needs it

For Each tbl In ActiveSheet.ListObjects
tbl.DataBodyRange.Rows.Clearcontents ' this is a great line, so thank you
Next tbl
Next ws
End Sub

 

[Alex Blakenburg]

In principle you try to avoid activate & select, so you can remove the
ws.Activate

and use the following instead of ActiveSheet

        For Each tbl In ws.ListObjects

 

[Raddle]

Alex

Thanks for that but that will not iterate through the next sheets' tables ...

This will only treat the active sheet.

 

[Raddle]

sorry should have put test version in .. but this doesn't work

Sub Acnt_ClearOutTablesTest() 'testing
Dim tbl As ListObject
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
'ws.Activate ' got rid of this as suggested

For Each tbl In ws.ListObjects
tbl.DataBodyRange.Rows.Clearcontents

Next tbl
Next ws
End Sub

 

[Raddle]

Update:

Sub Acnt_ClearOutTablesTest() 'testing
Dim tbl As ListObject
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
'ws.Activate

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

This is working - thank you for the tip on select and activate sheets .. will try to avoid that going forward