Hello garymrowe,
Welcome to the Forum!
The macro below will remove the merged cells on the "Source" worksheet. This has been added to the attached workbook.
'Written: February 02, 2011
'Author: Leith Ross
'Summary: Removes all merged cells in a table and fills the unmerged cells with merged
' cell value.
Sub RemoveMergedCells()
Dim C As Long
Dim Data As Variant
Dim LastCol As Long
Dim LastRow As Long
Dim MergeRng As Range
Dim R As Long
Dim Rng As Range
Dim Wks As Worksheet
Set Wks = Worksheets("Source")
Set Rng = Wks.Range("A1").CurrentRegion
Set Rng = Rng.Offset(1, 0).Resize(RowSize:=Rng.Rows.Count - 1)
LastCol = Rng.Columns.Count + Rng.Column - 1
LastRow = Rng.Rows.Count
Application.ScreenUpdating = False
For C = 1 To LastCol
For R = Rng.Row To LastRow
Set MergeRng = Wks.Cells(R, C).MergeArea
If MergeRng.Address <> Wks.Cells(R, C).Address Then
R = R + MergeRng.Rows.Count - 1
Data = MergeRng.Cells(1, 1).Value
MergeRng.UnMerge
MergeRng.FillDown
End If
Next R
Next C
Application.ScreenUpdating = True
End Sub
To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Bookmarks