Unmerge cells and fill duplicate data

[Dawn]

I have a report that contains merged cells. I need to get rid of the merged
cells and then have the data from the first row repeated in the blank rows.
I found a macro to umerge all the cells (Cells.MergeCells = False) so far so
good! But now I need to repeat the data. I have two columns in the sheet
that will need to be unmerged and have the data repeated.

The existing workbook looks like this, with the cells for W, X, Y, and Z
merged so they span one or more rows, it varies:

X Y 1 2 3
4 5 6
Z W 7 8 9
1 2 3

I need it like this, so I can successfully filter:

X Y 1 2 3
X Y 4 5 6
Z W 7 8 9
Z W 1 2 3

TIA for any help!

 

[Barb Reinhardt]

Try something like this


Option Explicit

Sub Test()

Dim r As Excel.Range
Dim r1 As Excel.Range
Dim myR As Excel.Range

For Each r In ActiveSheet.UsedRange
Debug.Print r.Address, r.Count
If r.MergeArea.Count > 1 Then
Set myR = r.MergeArea
r.UnMerge
For Each r1 In myR
r1.Value2 = myR.Value2
Next r1
End If
Next r

End Sub

HTH,
Barb Reinhardt

 

[ryguy7272]

Is the 4, 5, & 6 under the 1, 2, & 3? If so, and if you want to fill the
blanks below x & Y, look at this:
http://www.contextures.com/xlDataEntry02.html

If that doesn't work, please post back with a little more info.

Thanks,
Ryan--

 

[GES]

This is AWESOME! Thank you so much for saving me hours of time! I've always
wondered how to do this.
GES

 

[bigbreak8ball]

Add another column (B) and then add this function to fill in everything until another entry for the merged cell in col A:
=IF(A2=0,B1,A2).

This assumes you have one header.

 

[cibinlik]

Add another column (B) and then add this function to fill in everything until another entry for the merged cell in col A:

=IF(A2=0,B1,A2).



This assumes you have one header.

For two or more rows, this will not work.