Macro to Resize table with dynamic last row - not working - HELP!!

[daithiboy]

Hi folks,

This is probably very easy and amateurish, but, I am looking for a way to resize a table with a dynamic last row. When I record a macro of a resize with a set range I get:

Sub resizetable222()


    ActiveSheet.ListObjects("Table5").Resize Range("$A$5:$I$94")
    Range("I94").Select
End Sub

when i try and change the resize range to

("$A$5:lastcell)

it doesn't work i.e

Sub resizetable()


Dim lcell As Long
Dim lrow As Long
    
    lrow = ThisWorkbook.Sheets("Filter").Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox lrow
    
    lastcell = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
        
    Sheets("Filter").ListObjects("Table5").Resize Range($A$5, lastcell)
    
    
End Sub

As you can see I was messing around the lastrow also but could not get it to work either.

Any thoughts would be very much appreciated.

Many thanks in advance,
Dave

 

[Joe4]

Try replacing:

Range("$A$5:$I$94")

with:

Range("$A$5:$I$" & lrow)

 

[daithiboy]

Many thanks Joe.

I knew it was going to something easy!

Thanks again,
Dave

 

[Joe4]

You are welcome!