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.

--
Regards,
Tom Ogilvy



"Pete" wrote in message
m...
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