Merge and centre current row with next blank row if value is "Open" in column "A". VBA Program

[Akash030193]

I want to Merge and centre current row (row 2) with next blank row (row 3) if value is "Open". Similarly for other rows (6-7, 8-9, 10-11 etc.)

Can you please provide any suitable VBA Program?


[TABLE="class: grid, width: 0, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Status[/TD]
[TD]remark[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Why would you want to merge cells ?
They can / will cause all sorts of problems in the future with filtering, sorting, etc......but if you must

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("A" & r).Value = "Open" Then
        With Range("A" & r & ":A" & r + 1)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Merge
        End With
    End If
Next r
End Sub

 

[Akash030193]

Dear Michael M,

Wow... It is working........ Thanks a lot... very very much.

FYI. I will no need any further filtering, sorting etc. in future.

Regards
Akash Patel

 

[Michael M]

Ok then....glad to help....and thx for the feedback...

 

[Akash030193]

Thanks Michael,

Your program is working well. But further to your macro program. I need your help on below query!
I want to Merge and centre current merged row (row 2-3) with next blank row (row 4) if value is "Open" (merged row 2-3). Similarly for other rows (6-7, 8-9-10-11 etc.).

In short, I need to merge "open" comment with next blank cell even if the cell with "open" valve is merged or not.

Can you please provide any suitable VBA Program?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open (row 2 and 3 are merged)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Open (row 2 and 3 are merged)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Open (row 8-9-10 merged)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Open (row 8-9-10 merged)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Open (row 8-9-10 merged)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance

 

[Michael M]

Maybe this....but as mentioned previously, I would avoid mergede cells whenever possible

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("A" & r).Value = "Open" Then
        With Range("A" & r & ":A" & r + 2)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Merge
        End With
    End If
Next r
End Sub

 

[DanteAmor]

Try this

Sub merge_cell()
    Dim c As Range
    Application.DisplayAlerts = False
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
        If c(1).Value = "Open" Then
            c.HorizontalAlignment = xlCenter
            c.VerticalAlignment = xlCenter
            c.Merge
        End If
    Next
End Sub

 

[Akash030193]

DanteAmor,

Your macro program is not working. I tried to run but noting happen.

 

[DanteAmor]

If you have something like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:81.74px;" /><col style="width:96.95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >STATUS</td><td >REMARK</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Open</td><td style="font-weight:bold; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Close</td><td > </td></tr></table>


This is the result:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:81.74px;" /><col style="width:96.95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >STATUS</td><td >REMARK</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td rowspan="3" style="text-align:center; ">Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Open</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td rowspan="3" style="text-align:center; ">Open</td><td style="font-weight:bold; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Close</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Close</td><td > </td></tr></table>

---
Your data should be in column A and start in cell A2, and should only have the text "Open"


Tell me how do you have your data and where do you have it?

 

[Akash030193]

Hi Michael,

Your macro program is suitable to merge 2 merged rows with next blank row if the comment is open. (your command is r+2)

But not suitable for to merge 3 merged rows with next blank row as per the table (merged row 8-9-10 to be merged with 11).

Can you make me a program that can merge "open" comment with next blank cell, even if, the cell with "open" valve is merged with end number of rows, or not.

Let me explain you once again with new example:


[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C
remark[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Open[/TD]
[TD]as[/TD]
[TD]row 1-2 already merged[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]ad[/TD]
[TD]row 1-2 already merged[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]af[/TD]
[TD]This row (cell A3) To be merged with 1-2 (Cell A1-A2)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Open[/TD]
[TD]as[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]df[/TD]
[TD]This row (cell A5) To be merged with 4 (Cell A4)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Close[/TD]
[TD]vr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Open[/TD]
[TD]vf[/TD]
[TD]row 7-8-9-10 already merged[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 7-8-9-10 already merged[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 7-8-9-10 already merged[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 7-8-9-10 already merged[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]This row (cell A11) To be merged with 7-8-9-10 (Cell A7 to A10)[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


you have made your previous programs using r+1, r+2 formulas. And it is working. It can be better if You can make program that is having formula r+n type..........this will help me.