Shifting a merged cell down

1
votes

I am writing a VBA script that will shift a merged cell down. When it does this a pop-up box comes up warning the user that this operation will unmerge merged cells. 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("A28")) Is Nothing Then
    Range("c28").Insert Shift:=xlDown
End If

I would like one of two things to happen.

A. Always automatically accept Yes so the popup doesn't show to the user. (and I'll re-merge in VBA after the insert)

B. Is there a way to shift down without unmerging to begin with.

2
vbaexcelmerge
Have you merged A28 thru C28?? - Gary's Student
for case "A" you could use Application.DisplayAlerts = False in the beggining of the sub and Application.DisplayAlerts = True in the very end - Dmitry Pavliv
C28:D28 is the merged cell that is shifting down on the double click. Sorry about that. - Josh
@Josh, have you tried to use Application.DisplayAlerts = False as in my comment above? - Dmitry Pavliv
@Josh, btw, what are you going to shift? only cells in column C or entire row? - Dmitry Pavliv

2 Answers

1
votes

Based on your comments this should work and avoid the warning:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A28")) Is Nothing Then
        Range("c28:d28").Insert Shift:=xlDown
    End If
End Sub

If you still get the warning, try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A28")) Is Nothing Then
        Application.DisplayAlerts = False
        Range("c28:d28").Insert Shift:=xlDown
        Application.DisplayAlerts = True
    End If
End Sub
0
votes

If you have a situation where you don't know exactly which cells are merged, you can use the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("A28")) Is Nothing Then
        Application.DisplayAlerts = False
        Me.Range("c28").MergeArea.Insert Shift:=xlDown
        Application.DisplayAlerts = True
    End If
End Sub

But be aware that merged areas below row 28 can also be unmerged by this action. For example if say cells B30:F30 are merged the code above will cause them to unmerge.