Excel: Unmerge Cells

There is a manual method to unmerge cells by using Go To Special > Blanks. There's a set of instructions here: https://www.extendoffice.com/documents/excel/1139-excel-unmerge-cells-and-fill.html

However note carefully that this will fill down all blanks - even if that merged area is meant to be blank. To avoid this, add a space to these cells. The following procedure includes this extra step:

1. Select the columns that have merged cells.

2. Find and replace all blank cells with " " (single space):

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. And then select the range again.

5. Then click Home > Find & Select > Go To Special. In the Go To Special dialog box, select Blanks option.

6. And then click OK, all of the blank cells in the range have been selected. Then input = and press Up arrow key on the keyboard. See screenshot:

7. Then press Ctrl + Enter keys, all of the blank cells have been filled with the original merged data.

8. 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

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

'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