How to keep the formulas and clear the content in Excel using VB.net?

Aim to Achieve :

I have a range of data that I want to replace with new data.

So, I want to clear the old data and replace it with new data.

But the formulas in the old data are also required to work on new data.

I did the following :

Private Sub updateData(ByRef sheet As Excel.Worksheet, ByVal dataRow As String, ByRef data As Object)
    Dim range As Excel.Range
    downRange(sheet, dataRow).ClearContents()  // Note this !!
    // Both data and formulas are lost due to this.
    // What shall I use instead of this to retain the formulas in the first row.
    range = sheet.Range(dataRow.Substring(0, dataRow.Length - 1) + (data.GetLength(0) + 1).ToString())
    range.Value2 = data
    // If the 1st row still has the formulas, then I can do AutoFill for this new data.
End Sub

Private Function downRange(ByRef sheet As Excel.Worksheet, ByVal rangeString As String)
    Dim range As Excel.Range
    range = sheet.Range(rangeString)
    range = sheet.Range(range, range.End(Excel.XlDirection.xlDown))
    Return range
End Function

But, the problem is :

The formulas are getting lost. (Obviously)

I want to retain the formulas in the first row, so that I can then do AutoFill

Can you suggest some solution ?

Demo of required results:

OLD data :

  |   A      |  B  |  C  |
1 |   H1     |  H2 |  H3 |
2 |   =B2+C2 |  5  |  9  |   Therefore, A2 = 14
3 |   =B3+C3 |  7  |  2  |   Therefore, A3 = 9

I want to replace data and retain the formulas, so that (New data) :

  |   A      |  B  |  C  |
1 |   H1     |  H2 |  H3 |
2 |   =B2+C2 |  4  |  6  |   Therefore, A2 = 10
3 |   =B3+C3 |  3  |  5  |   Therefore, A3 = 8

And the given call is updateData(sheet, "A2:C2", dataFromDB)

How can I save the formulas but change the data without changing function call ?

Solution

I think you're almost there - it looks like you just need to start the clearing one row down from the top.

Assuming you have access to the Range.Offset method (I don't have an easy way to test this from Vb.Net but I see no reason why it shouldn't be available) then you should be able to just shift your starting cell:

Private Function downRange(ByRef sheet As Excel.Worksheet, ByVal rangeString As String)
    Dim range As Excel.Range
    range = sheet.Range(rangeString).Offset(1, 0) // Here's the extra piece
    range = sheet.Range(range, range.End(Excel.XlDirection.xlDown))
    Return range
End Function