How to Unmerge Cells and Fill Down Values in Excel

This post will guide you how to unmerge cells and fill with duplicate values in Excel 2013/2016. How do I unmerge multiple cells and copy the content in each previously merged cell in Excel. How to unmerge cells and fill down duplicate values with VBA Macro in Excel.

Unmerge Cells and Fill with Duplicate Values


Assuming that you have a list of data in range A1:C4, and some cells are merged in your table. And you want to unmerge cells in this range and fill each cell with the original value. How to achieve it.  You need to unmerge cells in selected range firstly, then select all blank cells with Go To Special feature, next you need to use a formula to fill down value in each blank cell. Just do the following steps:

#1 select the range of cells which contain merged cells.

unmerge cells fill values1

#2 go to HOME tab, click Merge & Center command under Alignment group, and select Unmerge Cells from the drop down menu list.

unmerge cells fill values2

#3 all merged cells in the selected range have been unmerged. And keep to select the range.

unmerge cells fill values3

#4 go to HOME tab, click Find & Select command under Editing group, and select Go To Special menu from the drop down menu list. And the Go To Special dialog will open.

unmerge cells fill values4

#5 select Blanks radio button in the Select section, and click OK button.

unmerge cells fill values5

#6 all blank cells have been selected. And then enter =, then press Up arrow in your keyboard.

unmerge cells fill values6

#7 press Ctrl + Enter keys on your keyboard, and all blank cells have been filled with the original merged value.

unmerge cells fill values7

Unmerge Cells and Fill with Duplicate Values using VBA Macro


You can also use an Excel VBA Macro to unmerge multiple cells in a selected range and then fill with duplicate values in each cell. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

unmerge cells fill values8

Sub UnmergeMulCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range which contain merged Cells", "UnmergeMulCells", myRange.Address, Type:=8)

    For Each myCell In myRange
        If myCell.MergeCells Then
            With myCell.MergeArea
                .UnMerge
                .Formula = myCell.Formula
            End With
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

unmerge cells fill values9

#6 Select one Range which contain merged Cells.

unmerge cells fill values10

#7 Let’s see the last result:

unmerge cells fill values11

Video: Unmerge Cells and Fill with Duplicate Values

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar