Delete Last Row in Table using VBA

Question

I used this code to remove the last row of data from a table.

The code works in isolation but when run as part of a larger set of code it does not remove the last row.

Sub TrimJrnl()
    
    Dim wsR2 As Worksheet
    
    Set wsR2 = ThisWorkbook.Sheets("Journal")
    
    lastrow = wsR2.ListObjects("xJrnl").Range.rows.Count
    
    rows(lastrow).Delete
    
End Sub

Answer 1

1. You need to count the rows of ListObjects("xJrnl").DataBodyRange so you know how many data rows are there (except header and summary rows).

2. You can access those data rows by ListObjects("xJrnl").ListRows(LastRow) and .Delete them.

Like Below:

Option Explicit

Public Sub TrimJrnl()
    Dim wsR2 As Worksheet
    Set wsR2 = ThisWorkbook.Sheets("Journal")
    
    Dim LastRow As Long
    LastRow = wsR2.ListObjects("xJrnl").DataBodyRange.Rows.Count
    
    wsR2.ListObjects("xJrnl").ListRows(LastRow).Delete
End Sub

A nice guide how to work with tables: The VBA Guide To ListObject Excel Tables