Microsoft Office Forums - View Single Post - Insert Row with Command Button Include Formulas and Merged Cells

TileGal · 08-29-2017, 12:21 PM

NoSparks, do you mind if I ask for your help one more time on this code?

I researched the issue of clearing the contents of the cells containing values only, and found code that works. The only problem is that my code clears the contents of the new row AND the source row. What change do I make to my code so that it clears the contents of ONLY the new row?

Code:

Private Sub CommandButton1_Click()
     Dim rowNum As Integer
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                    Title:="Insert Quote Row", Type:=1)
    If rowNum = 0 Then Exit Sub
    Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
    
    Rows(rowNum + 1).Copy
    
    Rows(rowNum).PasteSpecial Paste:=xlPasteFormats
    Cells(rowNum + 1, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
    Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False