Search code examples
excelmergeexcel-2007cells

How to keep value of merged cells in each cell?

I created a sheet with merged cells, but the value of the merged cells is only stored in the first cell. Is there anyway, to keep the same value in each of the cells, I need that for a formula I use. Thanks!

Solution

  • In Excel 2003 this macro does the job:

    Public Sub UnmergeAndFill()
    With Selection
        If .MergeCells Then
            .MergeCells = False
            Selection.Cells(1, 1).Copy
            ActiveSheet.Paste 'Or PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    End With
End Sub

    Create the macro by

    1. pressing Alt-F11, Ctrl-R, menu Insert/Module, paste the code;
    2. alternatively: Alt-F8, type a new name (UnmergeAndFill, e.g.), click Make or Create (? don't know the English button text)

    Invoke the Macro by pressing Alt-F8, select it, Run. Alternatively map it to a key