Unmerge Cells and Fill with Duplicate Values

**princ_wns** · 10-08-2012, 04:44 PM

Hi all,

I have range of data on my excel sheet and data is grouped and merged now what i want to unmerge these cells and fill the rows with the duplicate values.
For example:
My Actual data.

aaa sss r
t
h
ddd y
f
sd
eee w
w
sd
bbb fff y
j
o
kkk hj
d
sd
rr d
w
ccc ttt sd
sd
gt
yyy u
o
l
ccc hh gt
f
e
ew

required result

aaa sss r
aaa sss t
aaa sss h
aaa ddd y
aaa ddd f
aaa ddd sd
aaa eee w
aaa eee w
aaa eee sd
bbb fff y
bbb fff j
bbb fff o
bbb kkk hj
bbb kkk d
bbb kkk sd
bbb rr d
bbb rr w
ccc ttt sd
ccc ttt sd
ccc ttt gt
ccc yyy u
ccc yyy o
ccc yyy l
ccc hh gt
ccc hh f
ccc hh e
ccc hh ew

Thanks
Prince

**Excel Fox** · 10-08-2012, 05:00 PM

Try this...

Code:

Sub FillEmptyRanges()
    
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
    With rng
        .UnMerge
        .FormulaR1C1 = "=R[-1]C"
        For Each rng In rng
            With rng
                .Value = .Value
            End With
        Next rng
    End With
    
End Sub

**snb** · 10-08-2012, 08:55 PM

or

Code:

Sub snb()
    Cells(1).CurrentRegion.unmerge
    For Each ar In Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
        ar.Value = ar.Cells(1).Offset(-1)
        ar.Offset(, 1) = ar.Offset(, 1).Cells(1).Offset(-1)
    Next
End Sub

**LalitPandey87** · 10-09-2012, 07:36 AM

Hi Prince,

Below is the link where you can find the same as you want.

Fill Merge Cell Hierarchy | Power Desk

There i have uploaded an excel file with sample data with two different method. Use what is helpful to you.