Insert Row with Command Button Include Formulas and Merged Cells

[TileGal]

I am very new to VBA, and I understand coding. I need some help on a VBA code in Excel 2013.

A portion of my spreadsheet includes rows for quoting items. My end users are not very Excel savvy, so I have researched the code and cobbled together enough to create and use a button that will insert a row in the row number of their choice and include the formulas of the previous row (see below).

Where I'm getting stuck is keeping two sets of cells merged in the new row. Cells D(rowNum) through G(rowNum) should be merged, and H(rowNum) through J(rowNum). I've done lots of research on VBA code to insert a row and keep the merged cells, but I cannot figure out how to integrate it into my existing code. (This is where my lack of basic VBA rules knowledge comes in.)

I would really appreciate any help you can give, and if there's any additional information I need to provide, please let me know.

Thanks!!

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)
Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown

Rows(rowNum + 1).Copy

Rows(rowNum).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


End Sub

 

[Fluff]

Try

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)
Rows(rowNum + 1).Insert Shift:=xlDown
Rows(rowNum).Resize(2).FillDown

End Sub

PS It's best to avoid merged cells, where possible. Especially when using VBA

 

[TileGal]

Hey Fluff! Thank you! That did work! You're fabulous!

And yes, I'm well aware of the preference of avoiding merged cells, unfortunately, I was tasked to improve someone else's design and while I tried to remake the form from scratch without the merged cells, too much was lost when I tried. But thank you for the tip!!

Now I'm noticing (as I'm user testing) that the code is copying and pasting values from one row to another. Ideally, the new row would be blank (other than the formulas).

Is this doable??

Try

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)
Rows(rowNum + 1).Insert Shift:=xlDown
Rows(rowNum).Resize(2).FillDown

End Sub

PS It's best to avoid merged cells, where possible. Especially when using VBA

 

[Fluff]

Try this (you may need to switch the 2 pasteSpecial commands round)

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)
Rows(rowNum + 1).Insert Shift:=xlDown
Rows(rowNum).Copy
With Rows(rowNum + 1)
    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
End Sub

 

[TileGal]

Thanks Fluff, I gave that a try and tried swapping formulas/formats. It's still bringing down the values.

I'm trying to research this (in between being pulled away every 30 seconds by other matters), and what I think I'm seeing is that the code has to look inside the new row for cells that do not have formulas and clear their contents. Not sure how to code that yet...

Try this (you may need to switch the 2 pasteSpecial commands round)

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)
Rows(rowNum + 1).Insert Shift:=xlDown
Rows(rowNum).Copy
With Rows(rowNum + 1)
    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
End Sub

 

[Fluff]

OK this runs on Columns A to F, change the bits in red accordingly

Private Sub CommandButton1_Click()

    Dim rowNum As Integer
    Dim Cl As Range
    
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
    Title:="Insert Quote Row", Type:=1)
    
    Rows(rowNum + 1).Insert Shift:=xlDown
    Rows(rowNum).Resize(2).FillDown
    
    For Each Cl In Range("[COLOR=#ff0000]A[/COLOR]" & rowNum + 1 & ":[COLOR=#ff0000]F[/COLOR]" & rowNum + 1)
        If Not Cl.HasFormula Then Cl.Value = ""
    Next Cl
End Sub

.

 

[TileGal]

A little slow, but I'll take it!!!

As an aside, I had come up with this:

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

OK this runs on Columns A to F, change the bits in red accordingly

Private Sub CommandButton1_Click()

    Dim rowNum As Integer
    Dim Cl As Range
    
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
    Title:="Insert Quote Row", Type:=1)
    
    Rows(rowNum + 1).Insert Shift:=xlDown
    Rows(rowNum).Resize(2).FillDown
    
    For Each Cl In Range("[COLOR=#ff0000]A[/COLOR]" & rowNum + 1 & ":[COLOR=#ff0000]F[/COLOR]" & rowNum + 1)
        If Not Cl.HasFormula Then Cl.Value = ""
    Next Cl
End Sub

But it was clearing the new row AND the source row... couldn't figure out how to fix that!

Thank you!!

 

[Fluff]

But it was clearing the new row AND the source row

Not sure why it was doing that, as it worked fine for me. Unless it's something to do with what cells are merged.
But glad you got it sorted.