Macro - Resize table to last row

[jhallcb]

Hi,

What I need is a macro attached to a button that will resize the table down to the last row containing data. The problem I'm having is that due to the sheet being protected users cannot resize the table via the bottom corner.The table also doesn't update as it uses formulas and references to populate it. Inserting rows on mass and leaving blanks will in turn add blanks to my pivot table.

The macro will have to first unprotect the sheet and then resize the table more or less to fit the data.I have been scouring forums trying to find something on this but the only things I've seen give no answer.

I have the following code in another table that works perfectly to unprotect the sheet, add a now and protect again. I have tried to see if I can mod it in several ways but no joy. I've tried pieceing other bits together as well but nothing seems to have the desired effect.

Sub INS_Row()
'
' INS_Row Macro
'

'
ActiveSheet.Unprotect Password:="password"
ActiveSheet.ListObjects("DLog").ListRows.Add AlwaysInsert:=True
ActiveSheet.Protect Password:="password"
End Sub


If anyone could help or point me in the right direction I'd appreciate it. Running Excel 2007

 

[mole999]

many examples of LastRow or LR on here

Dim LR as Long
LR = Range("C165").End(xlUp).Row

then use .range("A"&LR) to set the scale (adjust for areas of interest)

 

[jhallcb]

mole999,

Thank you for the reply and sorry for my late response.
I've been playing with the below code but it is still not adjusting to the last row as desired.

Sub RSize()
Dim Bottom As Long

Bottom = Range("A65536").End(xlUp).Row

ActiveSheet.ListObjects("OH_Log_T3").Resize Range("$A$1:$G" & Bottom)
End Sub