Results 1 to 5 of 5

Merge cells in added row.

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Cool 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
    Last edited by leaning; 01-04-2012 at 08:47 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1