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

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

Thanks a lot..Mike
It worked properly.. =)

Then.. I have another case.
What Should I do If I have to fill blank cells?.. Based on cell abov
them
So.. It could be..

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

Thanks.

--
Mu
-----------------------------------------------------------------------
Mut's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52884

Almost the same:
Option Explicit

Sub CopyCells()
Dim LRow As Long
Dim MyRng As Range
Dim c 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
c.Value = c.Offset(-1, 0).Value
End If
Next c
End Sub

Mike F
"Mut" wrote in message
...

Thanks a lot..Mike
It worked properly.. =)

Then.. I have another case.
What Should I do If I have to fill blank cells?.. Based on cell above
them
So.. It could be..

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

Thanks..


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