VBA to Add Row and Copy Data from Row Below

[thelostscott]

Hi all,

I'm having trouble creating some code that copies and then deletes values & formulas from a row after I insert a row into a table by using:

Sub Add_Row()    
    Range("D4").Select
    Selection.End(xlDown).Select
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove  
End Sub

This code keeps data in the row below the newly inserted row, but what I want to do is to have the data (Values & Formulas) in the row below copied into the newly inserted row and then deleted from the original row.

Any ideas? (I hope that made sense!)

 

[Peter Thompson]

Assuming the rows are always going to be inserted in row 4 I think the below should work

Rows("5:5").Copy Destination:=Rows("4:4")

Rows("5:5").ClearContents

 

[mikerickson]

With Range("D4").End(xlDown)
    With .EntireRow
        .Insert
        .Offset(-1, 0).Value = .Value
        .ClearContents
    End With
End With

 

[thelostscott]

The Range is variable so mikerickson's code solves the issue perfectly! Thanks both for the feedback http://www.mrexcel.com/forum/member.php?u=73026

 

[thelostscott]

Actually I've just realised this only copies values. Is there any way to copy both values & formulas?

 

[mikerickson]

With Range("D4").End(xlDown)
    With .EntireRow
        .Insert
        .Offset(-1, 0).FormulaR1C1 = .FormulaR1C1
        .ClearContents
    End With
End With

 

[thelostscott]

Further alteration needed if possible? Need to keep the formulas in place during the .ClearContents action. Can this be easily done?

 

[mikerickson]

What do you mean by "keep the formulas in place during the .ClearContents"?
If you mean "clear only those cells that don't have a formula

With Range("D4").End(xlDown)
    With .EntireRow
        .Insert
        .Offset(-1, 0).FormulaR1C1 = .FormulaR1C1
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants).ClearContents
        On Error Goto 0
    End With
End With