I will have to unmerge while splitting data then copy visible cells. Not
sure how to do all that. But this is a start. What do you think?
Sub Unmerge()
Dim c
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
MsgBox c.Address & " is merged"
End If
Next
Dim rng As Range, rngtot As Range, rngval As Variant
Dim strtrow As Long, endrow As Long, col As Long

strtrow = Selection.Row
col = Selection.Column
endrow = Application.WorksheetFunction.Min(Selection.End(xl Down).Row - 1,
Cells(65536, col).End(xlUp).Row + 1)
rngval = Selection.Value

Set rngtot = Range(Cells(strtrow, col), Cells(endrow, col))

ActiveCell.Unmerge
For Each rng In rngtot
rng.Value = rngval
Next rng

End Sub

Gord Dibben wrote:
If you cannot or will not unmerge the cells, you are doomed.

What is preventing you from selecting the range and FormatCellsAlinment
and unmerging them?

No data will be lost

Gord Dibben MS Excel MVP

Merged cells stem from a database output which i have no control. So thanks
but no thanks!
[quoted text clipped - 12 lines]
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

--
Message posted via http://www.officekb.com