This is very close to what you want.

Option Explicit

Sub MergeCells()
Dim LRow As Long
Dim MyRng As Range
Dim c As Range
Dim MergRng As Range

Range("A1").Activate
LRow = ActiveCell.CurrentRegion.Rows.Count
Set MyRng = Range("A1:B" & LRow)

For Each c In MyRng
If c.Value = "" Then
Set MergRng = Range(c, c.Offset(-1, 0))
With MergRng.Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
End If
Next c
End Sub

I left in the merge cell format options so you can tweak as needed.

Mike F
"Mut" wrote in message
...

Dear all,
I have data in range A1:C5 like this..

A B C
1 Children Information News
2 Filler
3 News Filler
4 News News News
5 Movie Movie

Then.. I have to merge blank cell with cell above them.
So..
Cell A2 merge with Cell A1
Cell A5 merge with cell A4
Cell B3 and B2 merge with Cell B1

Can I do this automatically, using VBA code?
Because The data could be vary. And I have large data.

Any help would be appreciated.
Thanks.

Regards,
Mut


--
Mut
------------------------------------------------------------------------
Mut's Profile:
http://www.excelforum.com/member.php...o&userid=30633
View this thread: http://www.excelforum.com/showthread...hreadid=528841