Unmerging cells

[mplees]

Hi came across this code on the internet (I think originally created by Mike Rickson):

Dim oneCell As Range, onesFriends As Range
With ThisWorkbook.Activesheet
    For Each oneCell In .UsedRange
        If oneCell.MergeCells Then
            Set onesFriends = oneCell.MergeArea
            oneCell.UnMerge
            onesFriends.Value = oneCell.Value
        End If
    Next oneCell
End With

This almost does what I need - it unmerges all the merged cells, but each of the unmerged cells repeats the data that was originally in the merged cells. Is it possible to modify the code so that the cells are unmerged, but only the first of each unmerged cells contains the data?

Hope someone can help me out

Regards

Mark

 

[VoG]

Perhaps Erik's code http://www.mrexcel.com/forum/showthread.php?t=335123

 

[Jonmo1]

I would think removing this line would do that..

onesFriends.Value = oneCell.Value

 

[Rick Rothstein]

This single line of code will unmerge all merged cells on the ActiveSheet and leave the value that was in the merged cells in the upper left cell of each merged area....

Cells.Unmerge

 

[mplees]

Gentlemen,

Thank you both for your help - much appreciated.

regards

Mark

 

[Rick Rothstein]

Gentlemen,
Thank you both for your help - much appreciated.

Since you said "thank you both", that probably means you didn't see my original message yet (as I am the third responder), so I am responding again hoping you will notice the new message in your thread and come back to the forum to read it.

 

[mplees]

Rick,

My apologies - sorry I missed your post, and thank you for your assistance.

regards

Mark