VBA expand named table range error with variable in Cell function

Asked Mar 11 '21 at 17:59

Active Mar 11 '21 at 17:59

Viewed 38 times

I am trying to expand a named range table after I have inserted a new column before the first column. If I were to insert a column at the end, it would automatically add it to the table, but when adding it as the first column it does not.

I have tried the following but get a 'Subscript out of range error' when using a variable as the Lastrow entry instead of entering exactly the same number (I have watched the Lastrow variable get filled and am confident I am using the correct value).

What am I missing?

Sub InsertFirstCol()

Dim Lastrow As Integer

Worksheets("SHEETNAME").Activate
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Columns(1).Select
Selection.Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromRightOrAbove
ActiveSheet.ListObjects("Table150").Resize Range(Cells(2, 1), Cells(2, Lastrow))

End Sub

asked Mar 11 '21 at 17:59

Laucrimus

1

Subscript out of range means there is no table named `Table150` on the `ActiveSheet`. – BigBen Mar 11 '21 at 18:00
As @BigBen said you need to find out the right `Table name` – Rajput Mar 11 '21 at 18:03
Thank you! Of course! The Table Name is dynamic! – Laucrimus Mar 11 '21 at 18:05
1

Side note: use `Long` instead of `Integer`. Excel has more rows than `Integer` can handle. – BigBen Mar 11 '21 at 18:05

VBA expand named table range error with variable in Cell function

0 Answers0