VBA to ClearContents but keep Formulas!?

[thelostscott]

Can someone please give me some advice on adding a row whilst keeping formulas in place?

Currently I have the below code:

Sub Add_Row()
Application.ScreenUpdating = False
 
With Range("D14").End(xlDown)
    With .EntireRow
        .Insert
        .Offset(-1, 0).FormulaR1C1 = .FormulaR1C1
        .ClearContents
    End With
End With

Application.ScreenUpdating = True
End Sub

I need to keep the formulas in place in variable rows and not have them deleted.

Any Ideas?

 

[thelostscott]

Ah, that one would probably be thrown off because I have a summary table below this table that it will be trying to reference with the (xlUp) code. Apologies on that one.

I'm still unsure why this wouldn't work?

If Range("D14").End(xlDown).Offset(-1) <> "" Then

 

[VoG]

So should it be?

Sub Add_Row()
Application.ScreenUpdating = False
If Range("D14").End(xlDown).Value <> "" Then
    With Range("D14").End(xlDown)
        With .EntireRow
            .Copy
            .Insert
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0
        End With
    End With
End If
Application.ScreenUpdating = True
End Sub

Otherwise I don't think I understand what you are trying to do.