Thread: Merge cells in added row.

Hello!

I have this code to add a row below a selected line. It also allows for separate formats of each cell.

I can't get the syntax right for merging the column A and B cells together. I remmed out the line I am having trouble with. Any help appreciated!

Lost

Private Sub cmbAddRow_Click()
Dim c As Range
Dim ctr As Double
Dim MYROW As Long
Dim Irow As Long
Dim Myrng As String
Dim i As Integer
If Intersect(ActiveCell, Range("A31:J200")) Is Nothing Then
    MsgBox "Your cursor is at cell " & Selection.Address & ". Please place your cursor within the index."
GoTo endit
Else
If Intersect(ActiveCell, Range("A:A")) Is Nothing Then
  MsgBox "Your cursor is at cell " & Selection.Address & ". Your cursor needs to be in Column A in the row below which you wish to add rows."
  GoTo endit
End If
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="secret"
    Myrng = ActiveCell.Address
    Set c = Range(Myrng)
    
    c.Select
Dim nRows As Long
Dim lrow As Long
  MsgBox "You are going to add row(s) below " & Selection.Address & "."
  nRows = Application.InputBox("No. of rows to insert?", "Insert Rows", , , , , , 1)
  If nRows = False Then
 GoTo endit
  Else
  ActiveCell.Offset(1).Resize(nRows).EntireRow.Insert
  End If

Irow = nRows
    For i = 1 To Irow
    If Irow < 1 Then GoTo endit
    
       With c.Offset(i, 0)
            .Select
            .SetPhonetic
            .BorderAround ColorIndex:=1, Weight:=xlThin
            .Locked = False
            .FormulaHidden = False
            .WrapText = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .NumberFormat = "@"
        End With
   
        With c.Offset(i, 1)
            .Select
            .SetPhonetic
            .BorderAround ColorIndex:=1, Weight:=xlThin
            .Locked = False
            .FormulaHidden = False
            .WrapText = True
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .NumberFormat = "@"
        End With
   
    'Range(Cells(c, 0), Cells(c + nRows - 1, 1)).MergeCells = True
    

    
    Next i
End If
endit:
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Unprotect Password:="secret"
Range("A25:J25").Select
    ActiveCell.FormulaR1C1 = _
        "=StringConcat(""; "",'Quality Review'!R[-17]C[11]:R[-16]C[11], 'Quality Review'!R[-14]C[11]:R[2]C[11], 'Quality Review'!R[4]C[11]:R[18]C[11], 'Quality Review'!R[19]C[14], 'Quality Review'!R[20]C[4], 'Quality Review'!R[21]C:R[23]C)"
    Range("A28:J28").Select
    ActiveCell.FormulaR1C1 = _
        "=StringConcat(""; "",'Engineering Review'!R[-20]C[11]:'Engineering Review'!R[-6]C[11], 'Engineering Review'!R[-4]C[11]:R[7]C[11],'Engineering Review'!R[8]C[14], 'Engineering Review'!R[9]C[4], 'Engineering Review'!R[10]C:R[12]C)"
    Range("L25").Select
ActiveSheet.Protect Password:="secret"
End Sub

you cant have Cells(c, 0).
the first column "A" is index 1
use the following code instead:

Range(Cells(c,1), Cells(c + nRows - 1, 2)).MergeCells = True

please provide the paaswords to unprotect the sheet and view the vba code.
is it realy important for you to place the cursor on the last used cell in column A or can i modify the code to add the rows under the last cell??