Insert merged row into Excel, with duplication of the row(s) formatting above, using Command Button

[MikeKel]

Hello there!

I'm an enthusiastic if incredibly amateur Excel user- I usually just use google to figure things out but I'm hitting walls with this issue, so thank you in advance for any support or help you can offer!

I'm trying to build a feature into an Excel sheet, so that if you click on a Command Button (called +Row on this sheet), Excel will insert a new "row" into rows 107 & 108, but it will sort of just be one merged row. Essentially, it's duplicating 105+106 and inserting a copy into the two rows below, keeping all formatting etc. I'm aware that if this is possible it will lengthen the two merged yellow boxes to the right, and that's okay.

Is this possible? And if not, any ideas on how a similar thing can be achieved? I'm not precious about the merge, but the cells do need to be bigger due to the longer text my colleagues will enter into the section.

Thanks in advance!

 

[sxhall]

Hi MikeKel,

It is indeed possible, code below added to your button will do the above.

The only thing that is a potential issue is to know where the last row is to go. From your image it looks as though there are more rows below, the blue merged cell with an "X" in it. The code I've written will look for the text that is in that cell and will use the row when it finds it. You'll see in the below what to change so it can find this cell.

Sub Button2_Click()

'Used to set the row number to add in the new row
Dim AddRow As Integer
'Will search each cell in column A until it finds a match
For Each i In Range("A:A")
    'This checks the contents of the cell to see if it matches what we are looking for...
    If i.Value = "Will need to search for what is in this cell!" Then 'Change the text here so it knows where to find the row to add in the new rows, leave the " " marks at each end
    AddRow = i.Row ' Takes the row number of the cell (i) when it finds a match
    Exit For        'This means it will stop once it finds a match and continue where indicated...->
    Else
End If
Next

'---> continues from here once a match is found using our AddRow value as a reference point
Rows(AddRow - 2 & ":" & AddRow - 1).Copy                'Copies the two rows above for format
Rows(AddRow & ":" & AddRow).Insert Shift:=xlDown        'Inserts the copied cells
Range("A" & AddRow & ":E" & AddRow + 1).ClearContents   'Clear the cells contents
Range("F85:F" & AddRow + 1).Merge                       'Merges the cells in column F
Range("G85:G" & AddRow + 1).Merge                       'Merges the cells in column G
Range("A" & AddRow).Select                              'Selects the blank cell in the new rows added

End Sub

This is my mockup, you can see where the text is that it will be looking for.

Hope this what you require.

Steven