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?

 

[VoG]

Maybe like this

Sub Add_Row()
Application.ScreenUpdating = False
 
With Range("D14").End(xlDown)
    With .EntireRow
        .Copy
        .Insert
        .Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End With

Application.ScreenUpdating = True
End Sub

 

[thelostscott]

It comes up with an error saying that no cells were found?

 

[thelostscott]

Would it be easier to copy & paste formulas from the entire row above?

 

[VoG]

Try

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

Application.ScreenUpdating = True
End Sub

 

[thelostscott]

That's done it! Thanks VoG!

Is this also possible to add into the code?

If Range("D14").End(xlDown).Offset(-1, 0) = "" Then
Exit Sub
Else

What I'm trying to achieve is, if Range("D14").End(xlDown).Offset(-1, 0) is already blank then don't do anything otherwise crack on with the code above?

 

[VoG]

Try

Sub Add_Row()
Application.ScreenUpdating = False
If Range("D14").End(xlDown).Offset(-1) <> "" 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

 

[thelostscott]

Just tried that and it still adds on another row to the bottom of the already blank one?

Would it be doing this becase there are formulas in the same row? I just want it to ignore the code if cell reference "D(x)" is empty (x being variable).

 

[VoG]

Maybe like this

Sub Add_Row()
Application.ScreenUpdating = False
If Range("D" & Rows.Count).End(xlUp).Offset(-1) <> "" 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

 

[thelostscott]

Nope, no luck on that one. It won't add any rows at all?