VBA: Trying to delete all but the first row of a table

[rizzo93]

I want to delete all rows in a table EXCEPT for the first row in the databodyrange. Using tbl.DataBodyRange.Rows.Count, I plan to delete that many -1.

The problem I'm having is figuring out how to select my starting row in the databodyrange down to the last row of the table.

I thought of using this code, but not sure how to select more than one row with it (if that's even possible):

ActiveSheet.ListObjects("tbl").ListRows(3).Range.Select

Your recommendations, please?

 

[jkpieterse]

Why leave the first row?

With ActiveSheet.ListObjects(tbl")
    If .ListRows.Count > 0 Then
        .DataBodyRange.Delete
    End If
End With

 

[rizzo93]

Hi jkpieterse, thanks for the code. Simple and efficient!

I want to leave the first row so that when I paste new data into the left-most cell of the databodyrange (as plain text), the table will remain intact in terms of formatting and grow as needed.

 

[rizzo93]

Why leave the first row?

With ActiveSheet.ListObjects(tbl")
    If .ListRows.Count > 0 Then
        .DataBodyRange.Delete
    End If
End With

My row count will become increasingly larger, which will increase the time to delete them when I have to reset my data. While your code works, is there a way of selecting the rows I know that need to be deleted?

 

[jkpieterse]

Suppose you want to paste into a table which you first want emptied:

    With ActiveSheet.ListObjects("tbl")
        If .ListRows.Count > 0 Then
            .DataBodyRange.Delete
        End If
        'Code to copy goes here
        Range("C1:C3").Copy
        'Now we can paste
        .Range.Cells(1).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
    End With

 

[jkpieterse]

is there a way of selecting the rows I know that need to be deleted?

Selecting those rows will not speed up your code.

 

[rizzo93]

Thanks, again, jkpieterse. Much appreciated.

 

[mikerickson]

With Range("Table1")
    Application.Intersect(.Cells, .Offset(2, 0)).Delete
End With

 

[Fluff]

Another option

With ActiveSheet.ListObjects("tbl").DataBodyRange
   If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Delete
End With