Merge blank cell with cell above

**Mut** · 04-01-2006, 06:51 AM

Mike Fogleman · 04-01-2006, 11:00 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**Mut** · 04-03-2006, 04:03 AM

Mike Fogleman · 04-03-2006, 06:10 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>