Unmerge Cells and Fill with Duplicate Values

[princ_wns]

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]

Try this...

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]

or

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]

Hi Prince,

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

Fill Merge Cell Hierarchy | Power Desk (http://powerofexcel.wordpress.com/2012/10/08/fill-merge-cell-hierarchy/)

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