Excel: Unmerge Cells

3. Click Home > Merge & Center > Unmerge Cells. 

4. And the merged cells have been unmerged and only the first cell will be filled with the original values. 

5. Select the range again.

6. Click Home > Find & Select > Go To Special. In the Go To Special dialog box, select Blanks option. 

7. Click OK, all of the blank cells in the range have been selected. Then type = and press Up arrow key on the keyboard. 

8. Press Ctrl + Enter keys, and all of the blank cells will be filled with the original data repeated. 

See screenshot for a summary of steps 5 to 8:

9. Optional: Find-replace all space cells with blanks:

It's also possible to use a macro. This macro unmerges cells, and then copies the value of the merged cell into all unmerged cells.

Sub UnMergeSameCell()

'https://www.extendoffice.com/documents/excel/1139-excel-unmerge-cells-and-fill.html#a2

'Upadateby20131127

Dim Rng As Range, xCell As Range

xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each Rng In WorkRng

    If Rng.MergeCells Then

        With Rng.MergeArea

            .UnMerge

            .Formula = Rng.Formula

        End With

    End If

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

The following macro is similar to the above, but works with cells that are already unmerged and blank. For instance, this is common when using a Pivot Table, where repeated values are blank for ease of reading.

However it has the fault where purposely blank cells will be filled down. To avoid this, add a space to these cells.

Sub MacroFillAreas()

    ' Fill Down until Last Empty Row or Next Filled Cell

    ' https://stackoverflow.com/questions/30945950/fill-down-until-last-empty-row-or-next-filled-cell

    ' Modified to work with ranges by Edward Chan

    'I want to fill down until the last row (that contains any value at all) or the next cell within the column that contains information.

    '

    'The data looks like this

    '

    'a  1 2 3 4 5 6 7 8 9 10

    '   1 2 3 4 5 6 7 8 9 10

    '   1 2 3 4 5 6 7 8 9 10

    '   1 2 3 4 5 6 7 8 9 10

    '   1 2 3 4 5 6 7 8 9 10

    'b  1 2 3 4 5 6 7 8 9 10

    '   1 2 3 4 5 6 7 8 9 10

    'c  1 2 3 4 5 6 7 8 9 10

    Dim area As Range

    Dim s As Range

    Dim col As Range

    Set s = Selection

    For Each col In s.Columns

      For Each area In col.SpecialCells(xlCellTypeBlanks)

          If area.Cells.row <= ActiveSheet.UsedRange.Rows.Count Then

              area.Cells = Range(area.Address).Offset(-1, 0).Value

          End If

      Next area

    Next col

End Sub