Loop through three worksheets and delete table data in each sheet

Question

I've put together the below function to loop through three specific worksheets in a workbook and delete the table contents for tables in each worksheet.

I get

'Runtime Error 9, subscript out of range'

When I debug

For Each tbl In Sheets(Sht).ListObjects

is highlighted.

Sub ClearTableData()

    Application.ScreenUpdating = False
    
    Dim tbl As ListObject
    Dim Sht As Variant
    
    'Loop through each sheet and table in the workbook
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        For Each tbl In Sheets(Sht).ListObjects
            'Delete table rows
            tbl.DataBodyRange.Delete
        Next tbl
    Next Sht
    
    Application.ScreenUpdating = True

End Sub

Hi Andreas, when I look at the debug, the row 'For Each tbl In Sheets(Sht).ListObjects' is what's highlighted in yellow. — Marc
Subscript Error 9 means it doesn't exist. Do you have three sheets with those names? Not the codenames, but the sheet names on the tabs? — Darrell H

Marc Marc · Accepted Answer · 2020-12-03T13:58:44

I checked the spelling of the tabs in question and noticed someone had put a space at the end of 'Sheet2' hence it not being recognised. Thanks for the idea Darrell.

Loop through three worksheets and delete table data in each sheet

1 Answers