Hi have used the below code to remove the last row of data from a table. The code works ok in isolation but when run as part of a larger set of code it does not remove the last row. Any ideas on what is causing this and solution would be appreciated.

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

CodePudding user response：

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