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

NoSparks · 08-29-2017, 01:24 PM

I actually don't know why that would be clearing both rows as it's only being applied to the original row (rowNum+1)
although it is prior to pasting the contents even tho it's already copied.

I'd relocate the instruction and only apply it to the new row.

Code:

Private Sub CommandButton1_Click()
     Dim rowNum As Integer
     
'don't let Excel tell me about errors
'On Error Resume Next

'get rowNum to deal with
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                Title:="Insert Quote Row", Type:=1)

'incase user closes, cancels or doesn't enter a row number
If rowNum = 0 Then Exit Sub

'prevent screen flash
Application.ScreenUpdating = False

'insert new row
Rows(rowNum).Insert Shift:=xlDown

'copy original row
Rows(rowNum + 1).Copy

'paste formulas of original row
Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False

'paste formatting of original row
Rows(rowNum).PasteSpecial Paste:=xlPasteFormats

'clear cells with constants in newly inserted row
Cells(rowNum, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

'stop the marching ants
Application.CutCopyMode = False

'remove selection from entire new row
Range("A" & rowNum).Select

'turn screen updating back on
Application.ScreenUpdating = True

End Sub

Hope that helps.