Run Time Error 91 "Object variable or With Block variable not set"

[Sanjay Vernekar]

Hi all,

I am new to VB scripts . My requirement is to delete all the content of the table (Table structure should remain intact with headers) from all the worksheets in excel.
Each worksheets has multiple tables. Basically we are clearing all the table data so that it can freshly be entered.

I have written the code as below

Sub cleardata()

Dim tbl As ListObject
Dim sht As Worksheet

'Loop through each sheet and table in the workbook
For Each sht In ThisWorkbook.Worksheets

' Ignoring Contents and Cover Page sheet as its contains tables
If sht.Name <> "Contents" And sht.Name <> "Cover Page" Then
For Each tbl In sht.ListObjects

tbl.DataBodyRange.ClearContents

Next tbl
End If
Next sht
End Sub

This code worked in one workbook but not working in another as I am getting a run time error 91. Can you please guide me here.

Thanks in advance,
Sanjay

 

[jasonb75]

Which line causes the error?

 

[Sanjay Vernekar]

tbl.DataBodyRange.ClearContents

 

[jasonb75]

Probably a table with no body (headers created but no data added to it). Adding an error trap should correct that.

Sub cleardata()
    Dim tbl As ListObject
    Dim sht As Worksheet
        'Loop through each sheet and table in the workbook
For Each sht In ThisWorkbook.Worksheets

        ' Ignoring Contents and Cover Page sheet as its contains tables
    If sht.Name <> "Contents" And sht.Name <> "Cover Page" Then
        For Each tbl In sht.ListObjects
            On Error Resume Next
            tbl.DataBodyRange.ClearContents
            On Error GoTo 0
        Next tbl
    End If
Next sht
End Sub

 

[Sanjay Vernekar]

Thanks Jason , the provided solution worked