Extend Formula Range Using Excel Interop

Question

I'm using the Excel Interop libraries to take a sheet and replace values in the sheet. My sheet structure is as follows:

Columns A-E contain formula which work off the data in Columns F-L When I update the sheet, I clear the contents from F-L and leave any formula in A-E. I then fill columns F-L with new data.

I need my formula in Columns A-E(any rows) to extend to the full range of the new data I entered.

For example =sum(G1,G8) should become =sum(G1,G20) if I extend the data to 20 rows from 8. Is there a way to get to all present formula in the sheet? How do I extend the range of the formula?

Solution

While inputting data to the cells, keep track of each new cell and then apply the formula to that range:

sheet.Cells[rowCount, column].Formula = string.Format("=SUM(G1:G{0})", rowCount);