Unmerge cells and distribute contents in Excel (for mac) 2011

Question

I have a spreadsheet with a large amount of data in. About half the cells are merged horizontally with other cells and contain names e.g. John Doe. Does anyone know how to write a macro to unmerge the cells while distributing the value of the cell to all the cells that were previously merged? Cheers Jack

EDIT: The reason I am doing this is to check to see if two adjacent cells are equal i.e. is A1 = A2. But I run into problems when either cell is merged. If anyone knows a way around this problem without separating the cells and copying the data that would be even better!

Answer 1

The idea I provide below is tested for Excel 2010 VBA Win7. However, being not sure I hope it should work as for Mac, too (as this is rather set of standard properties and methods of Range object). If this doesn't work please let me know to delete my answer.

This simple code will work for selected area however it's quite easy to change it to any other range. Some other comment inside the code below.

Sub Unmerging_Selection()

    Dim tmpAddress As String
    Dim Cell As Range

    'change Selection below for any other range to process
    For Each Cell In Selection

        'check if cell is merged
        If Cell.MergeCells Then
            'if so- check the range merged
            tmpAddress = Cell.MergeArea.Address
            'umnerge
            Cell.UnMerge
            'put the value of the cell to
            Range(tmpAddress) = Cell
        End If
    Next

End sub

And the picture presenting before and after result:

Answer 2

I was able to get the solution from KazJaw to work on a mac with one edit, changing Cell.UnMerge to ActiveSheet.UsedRange.MergeCells = False, as provided by Ron Debruin here: http://www.rondebruin.nl/mac/mac027.htm.

Sub Unmerging_Selection()

Dim tmpAddress As String
Dim Cell As Range

'change Selection below for any other range to process
For Each Cell In Selection

    'check if cell is merged
    If Cell.MergeCells Then
        'if so- check the range merged
        tmpAddress = Cell.MergeArea.Address
        'umnerge
        ActiveSheet.UsedRange.MergeCells = False
        'put the value of the cell to
        Range(tmpAddress) = Cell
    End If
Next

End sub