Insert new line, copy from the line above then clear data in certain cells

Question

I am trying to create a Macro in Excel to place a line below whichever cell is highlighted, and then copy formulas only to that line in the correct cells.

So far I have managed to get the new line inserted and populated by copying the entire row contents.

What I need to know is how to I get the Macro to clear the data from specific cells, so I can leave the cells with formulas in intact?

I've tried the Selection.ClearContents command, but this just clears the entire rows data, including formulas.

Here is my code so far

Sub Button500_Click()


Dim lRow As Long
Dim lRsp As Long
'On Error Resume Next

lRow = Selection.Row()
'lRsp = MsgBox("Insert New row below " & lRow & "?", _
 '       vbQuestion + vbYesNo)
'If lRsp <> vbYes Then Exit Sub

Rows(lRow).Select
Selection.Copy
Rows(lRow + 1).Select
Selection.Insert Shift:=xlUp

'Paste formulas and conditional formatting in new row created
Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
Selection.ClearContents
Application.CutCopyMode = False

End Sub

Remove `On Error Resume Next`. **Never** use this line without a `On Error Goto 0`! This line hides **all** error messages but the errors still occur. If you don't see the messages you cannot fix the errors, if you don't fix them your code does not work. Fix them and [edit]/update your question. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Mar 08 '19 at 13:01
Thanks for the suggestion. Amended code now. and it did throw up an error, which I've resolved, however I am still at the same point, currently with a blank new line, I need to be able to copy the formulas from the line above but not the data — Jade Reynolds, Mar 08 '19 at 13:13
You end up because you `Selection.ClearContent` Please read the link I added and get rid of all `.Select` (or this will run you into errors soon or later). Then have a look at the [Range.SpecialCells method](https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.SpecialCells), using `xlCellTypeConstants` you can select all constant values and clear them (formulas will remain then). — Pᴇʜ, Mar 08 '19 at 14:12

score 0 · Answer 1 · answered Mar 08 '19 at 15:22

This will accomplish what you are trying to do using Selection.

With ActiveSheet.Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row, Columns.Count).End(xlToLeft))
    .Offset(1).Formula = .Formula
    .SpecialCells(xlCellTypeConstants, 23).Offset(1).ClearContents
End With

Insert new line, copy from the line above then clear data in certain cells

1 Answers1