Is there a way to delete rows with VBA without changing my cell references?
I’m doing a web query that I will refresh daily. For example, every time I pull data it populates column A for 20 rows. I then delete the first 10 rows. I then want to take the average of the remaining 10 rows. If I have the formula =AVERAGE(Sheet1!A1:A10) in a cell on Sheet2 then every time VBA deletes rows 1-10 the formula returns a #REF! error.
I know I can use VBA to insert “=AVERAGE(A1:A10)” back into the cell on Sheet2 once the deleting has been completed, but I have many formulas referencing that A1:A10 and most of them contain quotation marks. It’s slow to use VBA to reinsert these formulas every time I run the query and it’s going to be a headache writing up the code to insert each one of these and replacing all the quotations with double quotations, so I was wondering if there is a more efficient way.
And I must delete rows 1-10, I can’t just hide them.
I’m doing a web query that I will refresh daily. For example, every time I pull data it populates column A for 20 rows. I then delete the first 10 rows. I then want to take the average of the remaining 10 rows. If I have the formula =AVERAGE(Sheet1!A1:A10) in a cell on Sheet2 then every time VBA deletes rows 1-10 the formula returns a #REF! error.
I know I can use VBA to insert “=AVERAGE(A1:A10)” back into the cell on Sheet2 once the deleting has been completed, but I have many formulas referencing that A1:A10 and most of them contain quotation marks. It’s slow to use VBA to reinsert these formulas every time I run the query and it’s going to be a headache writing up the code to insert each one of these and replacing all the quotations with double quotations, so I was wondering if there is a more efficient way.
And I must delete rows 1-10, I can’t just hide them.
