vba - lastrow and excel table.

Question

I have tried to define a lastrow code that would return the last row value of the last non empty cell in a excel table. (ITS FORMATTED AS A TABLE)

My exceltable have values in COL A from 1 to 1005, COL B from 1 to 414 and in COL C from 414 to 1005.

What i want is to have one lastrow code that returns 414 and one that returns 1005. The problem i get is that because it is in a table. my codes

        lastrow3 = ThisWorkbook.Worksheets("Data?nskem?l").Range("A" & Rows.Count).End(xlUp).Row
        lastrow2 = ThisWorkbook.Worksheets("Data?nskem?l").Range("B" & Rows.Count).End(xlUp).Row

Both return 1005. Can i get around this with my table or is it a formating issue of some sort?

Best regards and thanks in advance /D

Answer 1

You will have issue if there is data below the excel table on the sheet. It's always better to refer the table column while finding the last row in an excel table.

Sub FindLastRowInExcelTableColAandB()
Dim lastRow1 As Long, lastRow2 As Long
Dim ws As Worksheet
Set ws = Sheets("Data?nskem?l")
'Assuming the name of the table is "Table1"
lastRow1 = ws.ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastRow2 = ws.ListObjects("Table1").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub