deleting all rows in a table without losing formula information vba

Question

I have a table that is:

This has formulae, and the number of rows in the table can vary. I want a code in VBA which I can apply to an Active Button that would delete all the rows of the table and turn it something into like this:

However I still want the new information input in the new rows to have the same formulae applied on them as on the old information that was present before. I do this currently manually by selecting the rows and deleting them; I have even tried creating a macro but this did not work.

Any help would be appreciated :)

Edit: This is the code the Macro generated:

Sub clear3()
'
' clear3 Macro
'

'
    Range("Table3").Select
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Range("F11").Select
End Sub

--Now this works alright on the table when there are two rows, however when the table has 3 or 1 or just any other number of rows; I get this:

Runtime Error : '9' Subscript out of range

Answer 1

' copy this into the Worksheet code module
Private Sub CommandButton1_Click()  'ActiveX button's click event handler
    Const TABLE_NAME = "TableN" ' replace with your table name
    Dim lo As ListObject
    
    On Error Resume Next
    Set lo = Me.ListObjects(TABLE_NAME)
    If Err.Number <> 0 Then
        MsgBox TABLE_NAME & " was not found. Check the table name", vbCritical + vbOKOnly, "Sub CommandButton1_Click()"
        Exit Sub
    End If
    On Error GoTo 0
    If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
End Sub

Edit2 (multiple table cleanup)

' copy this into the Worksheet code module
Private Sub CommandButton1_Click()  'ActiveX button's click event handler
    Dim lo As ListObject, TABLE_NAME
    ' Attention! tables on the same worksheet must not have the same rows/columns,
    ' otherwise you will get an error `Run-time error '1004': This operation is not allowed.
    ' The operation is attempting to shift cells in a table on your worksheet` or something like that.
    For Each TABLE_NAME In Array("Table1", "Table2", "Table3") 'and so on
        On Error Resume Next
        Set lo = Me.ListObjects(TABLE_NAME)
        If Err.Number <> 0 Then
            MsgBox TABLE_NAME & " was not found. Check the table name", vbCritical + vbOKOnly, "Sub CommandButton1_Click()"
            Exit Sub
        End If
        On Error GoTo 0
        If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
    Next
End Sub

Edit3 (tables on the different sheets)

' copy this into the Worksheet code module
Private Sub CommandButton1_Click()  'ActiveX button's click event handler
    Dim lo As ListObject, TABLE_NAME, arr
    For Each TABLE_NAME In Array("Sheet1|Table1", "Sheet2|Table2") 'and so on
        On Error Resume Next
        arr = Split(TABLE_NAME, "|")
        Set lo = Me.Parent.Sheets(arr(0)).ListObjects(arr(1))
        If Err.Number <> 0 Then
            MsgBox TABLE_NAME & " was not found. Check the table name", vbCritical + vbOKOnly, "Sub CommandButton1_Click()"
            Exit Sub
        End If
        On Error GoTo 0
        If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
    Next
End Sub

Attention! tables on the same worksheet must not have the same rows/columns, otherwise you will get an error Run-time error '1004': This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet or something like that.

An example of an acceptable table layout