VBA help deleting rows from table

[JARichard74]

I use this code to delete the number of rows based on a cell value.

ElseIf ThisWorkbook.Worksheets("Prep").ListObjects("Bidders").ListRows.Count > ThisWorkbook.Worksheets("Prep").Range("G1").Value Then
Set tbl = ThisWorkbook.Worksheets("Prep").ListObjects("Bidders")
lastrow = tbl.ListRows.Count
For x = lastrow To ThisWorkbook.Worksheets("Prep").Range("G1").Value Step -1
Set lr = tbl.ListRows(x)
lr.Delete
Next x

In this example I am going from a table with with 4 rows (excluding header row) Image1 to a table with two rows (excluding header row) Image2. The delete part works OK except that it also deleted content in the preceding row. I just want to delete the rows and keep content of remaining rows. Thanks for the help

 

[Zack Barresse]

Instead of deleting rows, I recommend clearing them and then resizing the Table to the desired size (of rows). Will be much faster than looping through the ListRows and deleting them one by one.

 

[JARichard74]

Thanks for the suggestion from a VBA newbie. I will research this and see what I can come up with. I have another bit of code that does the reverse i.e. adds rows. I'll see if resizing works there as well.

 

[Zack Barresse]

It does, and is infinitely faster with Tables.

In it's simplest form...

Public Sub TableInsertRows( _
        ByVal Table As ListObject, _
        Optional ByVal Count As Long = 1 _
    )

    Dim InsertRange As Range
    Dim ShowHeaders As Boolean
    Dim RowResize As Long
   
    RowResize = Table.Range.Rows.Count + Count
    If RowResize < 1 Then Exit Sub
   
    ShowHeaders = Table.ShowHeaders
    Table.ShowHeaders = True
    Table.Resize Table.Range.Resize(RowResize)
    Table.ShowHeaders = ShowHeaders

End Sub

You would call like this...

Public Sub RemoveTableRows()
   
    Dim Table As ListObject
    Dim RowValue As Range
   
    Set Table = ThisWorkbook.Worksheets("Prep").ListObjects("Bidders")
    Set RowValue = ThisWorkbook.Worksheets("Prep").Range("G1")
    TableInsertRows Table, -RowValue.Value
   
End Sub

This doesn't, however, make checks to see if there is data below the Table (if inserting), but can also be used for removing. If for removing, it doesn't clear the rows.

 

[JARichard74]

Hey Zack, used a couple of other approaches to delete the required number of rows. Latest one being

With tbl.DataBodyRange
    If .Rows.Count > bids.Value Then
      .Offset(1).Resize(.Rows.Count - 1).Rows.Delete
    End If
  End With

However, I always get the same result as my original post (see images). Cannot figure out what is causing it. Any suggestion?

 

[Zack Barresse]

If you don't want to resize and instead just delete, you can use something like this...

Public Sub Test_RemoveTableRows()
   
    Dim Table As ListObject
    Dim RowValue As Range
   
    Set Table = ThisWorkbook.Worksheets("Prep").ListObjects("Bidders")
    Set RowValue = ThisWorkbook.Worksheets("Prep").Range("G1")
    RemoveTableRows Table, RowValue.Value
   
End Sub

Public Sub RemoveTableRows( _
    ByVal Table As ListObject, _
    ByVal Count As Long _
    )

    Dim ClearRange As ListRow
    
    If Table.DataBodyRange Is Nothing Then Exit Sub
    If Count > Table.ListRows.Count Then Exit Sub
    
    Set ClearRange = Table.ListRows(Table.ListRows.Count - Count + 1)
    If Not ClearRange Is Nothing Then
        ClearRange.Range.Resize(Count).Delete
    End If

End Sub

In this example, note I was using a positive value in G1, opposed to the last example I passed it as negative because the function was versatile to add or remove rows.

 

[JARichard74]

I am OK with resizing. I am just very new to VBA. I used your original code to from 4 to 2. This is what I got. Probably just need code to clear the old lines 3 and 4

 

[JARichard74]

I got it working with your second code. Just modified to delete DataBodyRange - Range("G1") rows instead of just Range("G1"). Thanks a lot for your help. Much appreciated.