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
Bookmarks