vba: Unmerge and fill selection

[Babydum]

Hi,

I'm looking for code that will search for merged cells in the range A1:Z300, and where it finds them it will unmerge them and fill the freed cells with whatever content was in the merged group.

For example, if A1:A3 were merged and contained "Yes", I would want the macro to Unmerge them and put a "Yes" in cells A1, A2 and A3.

I don't know if it will make a difference, but some cells are merged down, some across.

Many thanks in advance for your help.

 

[Babydum]

Ah!

I found the answer here: http://www.mrexcel.com/board2/viewtopic.php?t=234538
and it works perfectly!

Thanks Erik

 

[Krishnakumar]

Sub UnMergeCells()
Dim Rng     As Range
Dim mCell   As Range
Set Rng = Range("A1:Z300")
Application.ScreenUpdating = False
For Each mCell In Rng.SpecialCells(xlCellTypeBlanks).Areas
    With mCell
        .UnMerge
        .Formula = "=R[-1]C"
        .Value = .Value
    End With
Next mCell
Application.ScreenUpdating = True
End Sub

 

[Babydum]

Thanks very much Kris