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