Clear multiple tables using arrays

[Eviltauro]

Hi all

I have a number of tables that I want to clear as a fore runner for importing data from a number of workbooks.

The code below works but it seems inefficient and believe it could be done better using an array.

I am very confused on how Listobjects would work as part of a loop using array so hoping someone may give me a nudge in the right direction.

Regards
Mark

Sub ClrTbls()


Application.DisplayAlerts = False

'Clear Worksheet Table
With Sheet19.ListObjects("TblWrk")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.ClearContents
.DataBodyRange.Delete
End If
End With

'Clear Recoveries Table
With Sheet12.ListObjects("TblRecov")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.ClearContents
.DataBodyRange.Delete
End If
End With

'Clear Forecast Table
With Sheet13.ListObjects("TblPipe")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.ClearContents
.DataBodyRange.Delete
End If
End With



Application.DisplayAlerts = True
End Sub

 

[steve the fish]

Not sure you will notice a huge difference but you could do this:

Application.DisplayAlerts = False

arr = Array(Sheet19.ListObjects("TblWrk"), Sheet12.ListObjects("TblRecov"), Sheet13.ListObjects("TblPipe"))
For i = LBound(arr) To UBound(arr)
    With arr(i)
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.ClearContents
            .DataBodyRange.Delete
        End If
    End With
Next

Application.DisplayAlerts = True

 

[igold]

Same same, I am not sure you gaining anything for efficiency, but you could automate the task, as there a number of ways you could fill the Array aTbls from a range on a sheet.

Sub test()
    
    Dim aTbls, i As Long, n As Long, t As Long
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    aTbls = Array("TblWrk", "TblRecov", "TblPipe")
    Dim tbl As Object
    For n = 1 To wb.Worksheets.Count
        With Sheets(n)
            For t = 1 To .ListObjects.Count
                For i = 0 To UBound(aTbls)
                    If .ListObjects(t).Name = aTbls(i) Then
                        .ListObjects(t).DataBodyRange.ClearContents
                        .ListObjects(t).DataBodyRange.Delete
                    End If
                Next
            Next
        End With
    Next
    
End Sub

 

[Eviltauro]

Hi "Steve the Fish"

Many thanks. I made a slight tweak and it now works like a dream.

Fair point about not much more efficient but it looks prettier now

Sub ClrTbls()
Dim arr As Variant


Set t1 = Sheet19.ListObjects("TblWrk")
Set t2 = Sheet12.ListObjects("TblRecov")
Set t3 = Sheet13.ListObjects("TblFrCst")
Set t4 = Sheet15.ListObjects("NonAvail")
Set t5 = Sheet21.ListObjects("TblNonStd")
Set t6 = Sheet6.ListObjects("TblCust")
Set t7 = Sheet7.ListObjects("TblPrint")
Set t8 = Sheet5.ListObjects("TblNrt")
Set t9 = Sheet4.ListObjects("TblNRTReport")

Application.DisplayAlerts = False


arr = Array(t1, t2, t3, t4, t5, t6, t7, t8, t9)
For i = LBound(arr) To UBound(arr)
With arr(i)
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.ClearContents
.DataBodyRange.Delete
End If
End With
Next


Application.DisplayAlerts = True
End Sub

 

[Eviltauro]

Hi igold

Thanks for the response and appreciate the different perspective.

Regards

 

[igold]

You're welcome. I am glad you got what you need from stf...