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


"Dawn" wrote:

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!