-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Insert Row with Merged Cells
- Thread starter pclark
- Start date
I don't think that will work because I can not determine what the new row number is, also it does not solve the insertion of multiple rows.
Here is what I am working with:
After x is answered the new row or rows need to have cells J:L and M:O merged.
Thanks again.
Here is what I am working with:
Code:
Private Sub CommandButton1_Click()
Dim ANS As String
Dim x As String
ActiveSheet.Unprotect password:="password"
ANS = MsgBox("Do you want to insert new rows here?", vbYesNo, "Insert Rows")
Select Case ANS
Case vbYes
x = InputBox("How many Rows do you want to insert?", "Number of Rows to Insert", "1")
Select Case x
Case vbOK
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert
ActiveSheet.Protect password:="password"
Case Else
ActiveSheet.Protect password:="password"
Exit Sub
End Select
Case vbNo
ActiveSheet.Protect password:="password"
Exit Sub
End Select
End SubAfter x is answered the new row or rows need to have cells J:L and M:O merged.
Thanks again.
Upvote
0
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
Try this. I reduced the unnecessary code, hope you don't mind.
Private Sub CommandButton1_Click()
Dim ANS As String, x As String
ANS = MsgBox("Do you want to insert new rows here?", vbYesNo, "Insert Rows")
If ANS = vbNo Then Exit Sub
x = InputBox("How many Rows do you want to insert?", "Number of Rows to Insert", "1")
If x = "" Then Exit Sub
Dim z As Long
z = ActiveCell.Row
ActiveSheet.Unprotect Password:="password"
ActiveCell.Resize(x).EntireRow.Insert
Range(Cells(z, 10), Cells(z + x - 1, 12)).MergeCells = True
Range(Cells(z, 13), Cells(z + x - 1, 15)).MergeCells = True
ActiveSheet.Protect Password:="password"
End Sub
Private Sub CommandButton1_Click()
Dim ANS As String, x As String
ANS = MsgBox("Do you want to insert new rows here?", vbYesNo, "Insert Rows")
If ANS = vbNo Then Exit Sub
x = InputBox("How many Rows do you want to insert?", "Number of Rows to Insert", "1")
If x = "" Then Exit Sub
Dim z As Long
z = ActiveCell.Row
ActiveSheet.Unprotect Password:="password"
ActiveCell.Resize(x).EntireRow.Insert
Range(Cells(z, 10), Cells(z + x - 1, 12)).MergeCells = True
Range(Cells(z, 13), Cells(z + x - 1, 15)).MergeCells = True
ActiveSheet.Protect Password:="password"
End Sub
Upvote
0
ray:Hello, thank you for this useful information. I have a question please:
Range(Cells(z, 10), Cells(z + x - 1, 12)).MergeCells = True - using this syntax when i insert 3 new rows with merge cells, it merge horizontal and vertical those cell and not creating 3 separate rows with merge cell by columns. What i must modify in the above code ?
Upvote
0
