vba to clear contents from 2 cells of newly added row (row number will change)

[TashaWard]

Hi all,
I have a worksheet with a macro button for user to add a new line
I currently have a code which will ask for permission to add a new line below the active cell, if confirmed it will copy the existing row and past underneath

What I'd like to do is to clear contents from 2 cells in this new row
I can't use specific cell references as users of the sheet may add more than one line so the selection needs to be of the row below active cell, to clear cells in columns B and C

Can someone help tweak this?

This is my current code:

ThisWorkbook.Worksheets("RequisitionForm").Unprotect ("*")
Dim lRow As Long
    Dim lRsp As Long
    On Error Resume Next


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


    Rows(lRow).Select
    Selection.Copy
    Rows(lRow + 1).Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
    ThisWorkbook.Worksheets("RequisitionForm").Protect ("*")
End Sub

 

[offthelip]

just add the following code at the bottom of your sub:

Cells(lRow + 1, 2) = ""
Cells(lRow + 1, 3) = ""