excel - Last row in column VBA

Question

I wrote a short VBA code to automate stuff.
A short snippet is as follows:

Sub TEST()

    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$148"), , xlYes).Name = _
        "Table2"

End Sub

However, every Excel file differs with regards to the number of rows. Now when I recorded this macro it just takes the range of $A$1:$O$148. How can I adjust this part so that it automatically recognizes the last row and/or range?

I already tried:

.Range("A1").SpecialCells(xlCellTypeLastCell).Row

Instead of:

Range("$A$1:$O$148")

Thanks in advance!

Answer 1

This is the way I do it and I'm guessing this is a duplicate, but you can mimic hitting End-Up from a row well below your used range with

finalRow = Range("A65000").End(xlup).Row

then you can do

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$" & finalRow & ""), , xlYes).Name = _
    "Table2"