Resize Listobject Table dynamically with VBA
The problem is Range("A1" & Lrow1)
returns a Range of $A$112
, because you are passing the Range
function the result of the catenation of "A1" & "12"
.
Try replacing this line:
ob.Resize Range("A1" & Lrow1)
With this one:
ob.Resize ob.Range.Resize(Lrow1)
If you need to resize only the row-dimension:
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("YourTableName")
With tbl.Range
tbl.Resize .Resize(.CurrentRegion.Rows.Count) 'NOTE: unlike the Range.Resize proprty, the Table.Resize
'method's argument is a Range object (not a size spec).
End With
Resizing only the column-dimension would be symmetrical:
With tbl.Range
tbl.Resize .Resize(, .CurrentRegion.Columns.Count)
End With
There's way avoiding calculating last row:
Sub ResizeListDyn()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
tbl.Resize tbl.Range.CurrentRegion
End Sub