Merge blank cell with cell above

  • Thread starter Thread starter Mut
  • Start date Start date
M

Mut

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
 
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
 
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.
 
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
 
Back
Top