Search code examples
excelvbaforeachlistobject

how to use .DataBodyRange.Delete for each Listobject table in a workbook


I am attempting to loop through multiple tables within a single workbook and clear the data on those tables so that new data can be entered. As the row counts, column counts, names, and number of tables can change I was hoping to create a single macro to loop through the tables and perform .DataBodyRange.Delete

Working Code for 1 table called by sheet and object name:

I also found this code on a forum and I believe the IF portion takes into account a table that is already empty so that no error is thrown.

Sub ResetTable()
With ThisWorkbook.Sheets("SheetName").ListObjects("ListObjectName")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
End Sub

My attempt at turning this into a more dynamic loop:

The second set of code is failing on With ws .tbl due to a Compile error: Method or data member not found. So I am looking for any help in either fixing the below code or using an entirely different path.

Sub ResetAllTables()

Dim tbl As ListObject
Dim ws As Worksheet

'Loop through each sheet and table in the workbook
  For Each ws In ThisWorkbook.Worksheets
    For Each tbl In ws.ListObjects
      'Do something to all the tables...
        With ws.tbl
            If Not .DataBodyRange Is Nothing Then
                .DataBodyRange.Delete
            End If
        End With
        tbl.ShowTotals = True     
    Next tbl
  Next sht
End Sub

Thanks in advance!


Solution

  • as suggested by BigBen & SRJ,

    changing with ws.tbl to just with tbl fixed the issue. also had to fix Next sht to be Next ws

    Thanks for the help!