excel - Is it possible to delete and recreate a table object in VBA without de-referencing the formulas in the workbook? -

i have table object in sheet used many formulas in workbook.

the table object created in vba routine.

the issue formulas refering table broken if table re-created in vba routine. possible avoid this.

for example

=sumifs(output_dump[value],output_dump[assetclass],"ml") 

gets broken when table deleted , recreated same name during vba routine

=sumifs(#ref!,#ref!,"ml") 

is there way of locking formulas in sheet or preventing them updating during vba routine?

just insert rows in table , references of updated encompass new data rows range

option explicit

sub main()     dim tbl listobject      set tbl = worksheets("table").listobjects("output_dump") '<--| change "table" actual worksheet name      tbl.databodyrange.rows(2) '<-- reference data 2nd row         .insert '<-- insert row -> you'll have new empty row between data row 1 , 2         .offset(-1).cells(1, 1) = "ml" '<-- fill inserted row row index (.offset(-1)) first column (.cells(1, 1))         .offset(-1).cells(1, 2) = 100 '<-- fill inserted row row index (.offset(-1)) second column (.cells(1, 2))     end end sub