Lastrow in VBA no longer works with Table

[anichols]

Hello all,

I have a piece of VBA that identifies the last row in a column, so that I can assign values:

Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Lastrow).Offset(1, 1).Select
BankNo = ActiveCell

My issue, is I recently formatted the data on the worksheet as a table, so that visually it is easy to read and edit, and to delete and add rows. Now, my Lastrow doesn't work as it selects the row after the end of the table instead of the first blank row in Column A. Is there something simple I can change in my code to look for my blank values in the table?

 

[RoryA]

Test the last cell in the relevant table column. If it's blank, do your End(xlUp) from there.

Seems a slightly odd thing to need to do in a table though.

 

[anichols]

Test the last cell in the relevant table column. If it's blank, do your End(xlUp) from there.

Seems a slightly odd thing to need to do in a table though.

I'm not sure how to go about that. I tried adding the tablename in place of the sheet, but that only threw an error.

 

[RoryA]

For example:

   Dim lastRow As Long
   With ActiveSheet.ListObjects(1).ListColumns(1).DataBodyRange.Cells
      If IsEmpty(.Item(.Count)) Then
         lastRow = .Item(.Count).End(xlUp).Row + 1
      Else
         lastRow = .Item(.Count).Row + 1
      End If
   End With

lastRow will be the row number after the last row with data (so the row underneath the table if the last cell in that table column has data already).