PDA

View Full Version : [SOLVED] VBA to add new row above and include formatting



mykal66
11-02-2020, 07:17 AM
Hi

I have this piece of code that is use to add a 'ADD NEW ROW' button above the command button in workbooks. It has worked fine up until a new workbook where there are merged cells in the row. The code add a new row, includes lists, conditional formatting, borders etc but where cells should be merged it is splitting them (but borders look ok).

Does anyone know how to adapt this code to include the merged cells as well please?

Thank you

Mykal


Private Sub CommandButton1_Click()
'Insert row above active cell
'ActiveCell.EntireRow.Insert

For Each obj In ActiveSheet.OLEObjects
If obj.Name = "CommandButton1" Then
s = Intersect(obj.TopLeftCell, Cells).Address
Range(s).EntireRow.Insert Shift:=xlUp
Exit Sub
End If
Next
End Sub

snb
11-02-2020, 09:15 AM
Yes, remove alle merged cells.
Use an 'intelligent' table.

mykal66
11-03-2020, 04:10 AM
Thanks, removed all merged cell and all good now