Merge Cells

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I wish to merge all empty cells in a selection with the first non
empty cell above retaining that value e.g.

Smith
<empty>
<empty>
Jones
Bloggs
<empty>

Smith)
)Merged
)
Jones
Bloggs)
)Merged

and so on...

any ideas?

Pete
 
Sub MergeCells()
Dim rng As Range, cell As Range, ar As Range
Set rng = Selection.SpecialCells(xlBlanks)
For Each ar In rng.Areas
Set cell = ar.Offset(-1, 0).Resize(ar.Rows.Count + 1)
cell.Merge
Next
End Sub


Special cells is restricted to the usedrange, so make sure the cells at the
bottom in the selection are included in the usedrange.
 
Back
Top