Excel VBA: how to insert a new row into a range and copy formulas

Question

Hi There

I have a named range like the following covering A2:D3

ITEM    PRICE   QTY SUBTOTAL
1           10  3   30
1           5   2   10
           TOTAL:   40

I am looking for some VBA code to insert a new row into the range copying the formulas not values.

Any tips/links greatly appreciated.

Answer 1

If you start recording a macro and actually do the task in hand, it will generate the code for you. Once finished, stop recording the macro and you'll have the code needed which you can then amend.

Answer 2

This should help you: http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Answer 3

This should do it:

Private Sub newRow(Optional line As Integer = -1)
Dim target As Range
Dim cell As Range
Dim rowNr As Integer

    Set target = Range("A2:D3")

    If line <> -1 Then
        rowNr = line
    Else
        rowNr = target.Rows.Count
    End If

    target.Rows(rowNr + 1).Insert
    target.Rows(rowNr).Copy target.Rows(rowNr + 1)
    For Each cell In target.Rows(rowNr + 1).Cells
        If Left(cell.Formula, 1) <> "=" Then cell.Clear
    Next cell
End Sub