Clear table contents and leave first row

[DE_Tx]

I need some help with what I think to be a simple macro to delete all the data rows from a table. I've found a couple of threads on another forum but can't get them to work and don't understand them. I know, I should have come here first.

Here is what I currently do. I have a table named f_TOD_Data. The sheet name where the data resides is TOD.

Goto f_TOD_Data (this highlights all the data, but you probably already know that)
Ctrl+- (- on the keypad, this deletes all the rows of data)

When I do that with the macro recorder I get this huge long list of
Selection.ListObject.ListRows(1).Delete

So, I know there has got to be an easier way.


Any assistance would be greatly appreciated.

 

[AlanSidman]

Option Explicit

Sub clearTbl()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(2, 1), Cells(lr, lc)).ClearContents
End Sub

 

[DE_Tx]

Thanks, Alan. Here is what I ended up using from another forum. The code was provided by Roy UK and edited by Oldtechaa.

Sub Macro3()
    With Sheet1.ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
End Sub

I originally tried this and changed Sheet1 to my sheet name and Table1 to my table name. But, I didn't realize that I needed to leave Sheet1 as is. I noticed this when looking at the VBA editor and seeing Sheet1(MySheetName). Once I changed the code back to Sheet1, it worked fine.

As you can probably tell, I am new to VBA and figure this stuff out mainly by trial and error.

I hope my learnings help others.

TcO

 

[kelvin-lkh]

Sheet 1, 2, 3 and etc is the default sequence of worksheet in a workbook regardless what the worksheet name has been change to.

if you want the macro to run at your specified worksheet name, then you should modify the code as below

Sub Macro3()
With Worksheets("MysheetName").ListObjects("Table1")
IfNot .DataBodyRange IsNothingThen
.DataBodyRange.Delete
EndIf
EndWith
EndSub