How to Find the Next non-empty cell using vba excel (plus merge it) one step further

[ACRAENS]

I would like to join and merge the cells in column B using the "merged rnages" in column A
On top of that the same for column c.

I used the solution from jim may but run into trouble.

[h=2]"

Re: How to Find the Next non-empty cell using vba excel (plus merge it)
[/h]

Sample Table BEFORE Running of Macro (Foo)...

Excel 2010

	A	B	C	D
COL_A_HEADER	B	C	D
AAA
BBB
CCC
DDD

<colgroup><col style="background-color: #DAE7F5"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"] 1 [/TD]

[TD="align: center"] 2 [/TD]

[TD="align: right"] 121 [/TD]
[TD="align: right"] 359 [/TD]
[TD="align: right"] 236 [/TD]

[TD="align: center"] 3 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 395 [/TD]
[TD="align: right"] 175 [/TD]
[TD="align: right"] 255 [/TD]

[TD="align: center"] 4 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 198 [/TD]
[TD="align: right"] 449 [/TD]
[TD="align: right"] 255 [/TD]

[TD="align: center"] 5 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 238 [/TD]
[TD="align: right"] 133 [/TD]
[TD="align: right"] 151 [/TD]

[TD="align: center"] 6 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 254 [/TD]
[TD="align: right"] 208 [/TD]
[TD="align: right"] 109 [/TD]

[TD="align: center"] 7 [/TD]

[TD="align: right"] 437 [/TD]
[TD="align: right"] 464 [/TD]
[TD="align: right"] 349 [/TD]

[TD="align: center"] 8 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 477 [/TD]
[TD="align: right"] 436 [/TD]
[TD="align: right"] 358 [/TD]

[TD="align: center"] 9 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 223 [/TD]
[TD="align: right"] 290 [/TD]
[TD="align: right"] 383 [/TD]

[TD="align: center"] 10 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 178 [/TD]
[TD="align: right"] 124 [/TD]
[TD="align: right"] 148 [/TD]

[TD="align: center"] 11 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 270 [/TD]
[TD="align: right"] 335 [/TD]
[TD="align: right"] 128 [/TD]

[TD="align: center"] 12 [/TD]

[TD="align: right"] 492 [/TD]
[TD="align: right"] 249 [/TD]
[TD="align: right"] 428 [/TD]

[TD="align: center"] 13 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 170 [/TD]
[TD="align: right"] 366 [/TD]
[TD="align: right"] 131 [/TD]

[TD="align: center"] 14 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 294 [/TD]
[TD="align: right"] 470 [/TD]
[TD="align: right"] 420 [/TD]

[TD="align: center"] 15 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 411 [/TD]
[TD="align: right"] 204 [/TD]
[TD="align: right"] 261 [/TD]

[TD="align: center"] 16 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 368 [/TD]
[TD="align: right"] 107 [/TD]
[TD="align: right"] 293 [/TD]

[TD="align: center"] 17 [/TD]

[TD="align: right"] 208 [/TD]
[TD="align: right"] 300 [/TD]
[TD="align: right"] 353 [/TD]

[TD="align: center"] 18 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 129 [/TD]
[TD="align: right"] 445 [/TD]
[TD="align: right"] 289 [/TD]

[TD="align: center"] 19 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 354 [/TD]
[TD="align: right"] 185 [/TD]
[TD="align: right"] 292 [/TD]

[TD="align: center"] 20 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] 365 [/TD]
[TD="align: right"] 430 [/TD]
[TD="align: right"] 455 [/TD]

</tbody>

Sheet1​

Paste below into a standard Module:

Code:
Sub Foo()
Dim arr()
Dim LR As Long, ct As Long, t As Long
Dim Rng As Range, c As Range
Application.ScreenUpdating = False
LR = Range("B" & Rows.Count).End(xlUp).Row
ct = 1
Set Rng = Range("A2:A" & LR)
For Each c In Rng
If c.Value <> "" Then
ReDim Preserve arr(ct)
arr(ct) = c.Row
ct = ct + 1
End If
Next c
For t = 1 To UBound(arr) - 1
With Range(Cells(arr(t), "A"), Cells(arr(t + 1) - 1, "A"))
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next t
With Range(Cells(arr(t), "A"), Cells(LR, "A"))
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub




Back up your Data before trying this..."
​

Any help is welcome