VBA: Delete all rows down to End of Table

[zero269]

Hello,

I've created a test Macro to delete rows from an Excel Table. However, considering the number of rows change after each import, I'm trying to figure out how to delete Row #3 all the way down to the end of the Table... leaving the Total row alone when applicable.

The Macro shows multiple entries for deleting rows(2) over and over again.

    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete

Is there a way to tell it to go to the end instead of repeating?

Note: I'm just beginning to play with VBA code...

 

[zero269]

I found this tucked into a YT video, but I'm not confident that this will work inside an Excel Table, versus a range of cells that just look like a table.

Worksheets("Sheet1").Rows(3 & ":" & Worksheets("Sheet1").Rows.Count).Delete

 

[vcoolio]

Hello Zero269,

You may only need something like this:-

Sub TestDelete()

        Dim ws As Worksheet: Set ws = Sheets("Your sheet name here")
        
        With ws.ListObjects("Table1").DataBodyRange  '----> Change table name to suit.
               .Resize(.Rows.Count, .Columns.Count).Rows.Delete
        End With

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[zero269]

Hello Zero269,

You may only need something like this:-

Sub TestDelete()

        Dim ws As Worksheet: Set ws = Sheets("Your sheet name here")
     
        With ws.ListObjects("Table1").DataBodyRange  '----> Change table name to suit.
               .Resize(.Rows.Count, .Columns.Count).Rows.Delete
        End With

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,

Thank you for your suggestion, but unfortunately, it's deleting all of the rows. The first row is present, but the cells are empty.
I'm looking to remove Row 3 and below only within the Table. I need to keep Row 2 because I have a formula in the last column that I need when the Table is Updated from a Web Connection.

I was following along with a YT Video "VBA - Looping through a Table (ListObject) and deleting a row", but he's deleting rows based on a cells value, not the Tables row number.

If I can delete Rows 3 to the Last row, that would be optimal.
I can clear the necessary cells in the 2nd row using my original macro code:

Range("D2:N2").Select
Selection.ClearContents

 

[Peter_SSs]

Does this do what you want?

Sub test()
  With ActiveSheet.ListObjects(1)
    .Resize .Range.Resize(2)
  End With
End Sub

 

[vcoolio]

Perhaps this will suffice:-

Sub Test()

    Dim ws As Worksheet: Set ws = Sheets("Your sheet name Here")
    
    With ws.ListObjects("Table1").Range
           Application.Intersect(.Cells, .Offset(2)).Delete
    End With

End Sub

Cheerio,
vcoolio.

 

[zero269]

Does this do what you want?

Sub test()
  With ActiveSheet.ListObjects(1)
    .Resize .Range.Resize(2)
  End With
End Sub

Hi Peter,

Thanks for taking a stab at this. Unfortunately, this code doesn't reference a Table, so it deleted the rows in the Table in Column A and B.
No biggie since I'm working with a Copy of the original file.
One thing to note is that although it deletes the Rows within the Table, the values remained in place. I thought that was pretty interesting.

I stumbled upon another Mr. Excel thread requesting the exact same thing. Although the OP never returned to share the solution, NoSparks provided a link to an external source providing some pretty useful information on using VBA with Tables. I took a look, and surprisingly I found exactly what I needed:

Delete all data rows from a table (except the first row)

Sub ResetTable()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Delete all table rows except first row
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With

'Clear out data from first table row
  tbl.DataBodyRange.Rows(1).ClearContents

End Sub

I commented out the tbl.DataBodyRange.Rows(1).ClearContents line at the end because I need to keep the formula in the last column.
However, I'm happy to say that after spending several hours online today, reading, watching, testing... I can finally say today was successful.

Thank you...

 

[Peter_SSs]

Hi Peter,

Thanks for taking a stab at this. Unfortunately, this code doesn't reference a Table,

Yes it does, it references whatever Excel thinks of as table, index 1 on the worksheet. If you have more than one table, you simply have to point the code at your particular table. Since you didn't tell us that there were multiple tables, or what the particular table name or index number was, I used index 1.

One thing to note is that although it deletes the Rows within the Table, the values remained in place.

Since you asked to delete the rows, I assumed that you had already removed any data that you wanted to remove.

Try this instead

Sub test2()
  With ActiveSheet.ListObjects("Table1") '<- Adjust to your table name
    With .DataBodyRange
      If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
      .Resize(, .Columns.Count - 1).ClearContents
    End With
    .Resize .Range.Resize(2)
  End With
End Sub

 

[Alex Blakenburg]

I have to missing something.
Just deleting the databody range deletes all the data rows AND leaves any formulas available. (They will reappear as soon as you enter data)

Sub testdelete()

    Dim lo As ListObject

    Set lo = Range("Table1").ListObject
    
    lo.DataBodyRange.Rows.Delete
End Sub

 

[zero269]

Yes it does, it references whatever Excel thinks of as table, index 1 on the worksheet. If you have more than one table, you simply have to point the code at your particular table. Since you didn't tell us that there were multiple tables, or what the particular table name or index number was, I used index 1.


Since you asked to delete the rows, I assumed that you had already removed any data that you wanted to remove.

Try this instead

Sub test2()
  With ActiveSheet.ListObjects("Table1") '<- Adjust to your table name
    With .DataBodyRange
      If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
      .Resize(, .Columns.Count - 1).ClearContents
    End With
    .Resize .Range.Resize(2)
  End With
End Sub

Hi Peter,

Thanks for taking the time to follow up with me.

Regarding the "no table reference", I wasn't sure how to read your original code, so I didn't understand the Index reference you mentioned. I'm just beginning to learn VBA. Fell asleep watching a course on LinkedIn Learning last night.

I tested your new code, and it successfully deletes Table Row 3 to the end of my specified table: tbl_BooksSource. This works like a charm, and once I can figure out how to read it, I think this will serve a better purpose than the one I'm using now to do the same thing for the two Tables I have on one sheet.

It's not as clean as I'd like, but that will come later once I getter a better handle on understanding VBA code. Here's what I'm currently using for my two tables if curious:

Sub URLs_Reset_Tables()

Dim tbl1 As ListObject
Set tbl1 = ActiveSheet.ListObjects("tbl_URLs")

'Delete all table rows except first row
  With tbl1.DataBodyRange
    If .Rows.Count > 2 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
  
Dim tbl2 As ListObject
Set tbl2 = ActiveSheet.ListObjects("tbl_BooksSource")

  With tbl2.DataBodyRange
    If .Rows.Count > 2 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
  
'Clear out data from first table row (keeping end columns with formulas)
'  tbl2.DataBodyRange.Rows(1).ClearContents

    Range("A4").Select
    Selection.ClearContents
    Range("D4:N4").Select
    Selection.ClearContents
    Range("A4").Select

End Sub