Delete Last Row in Table vba

user_19145820 0
Viewed 317

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
vba excel
1 Answers

  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

user_32196130
Related