Microsoft Office Forums - View Single Post - [Solved] How to unmerge all merged cell and fill them with the merged values?

p45cal · 11-10-2019, 11:36 AM

try:

Code:

Sub blah()
For Each cll In ActiveSheet.UsedRange.Cells
  If cll.MergeCells Then
    myValue = Empty
    myFormat = Empty
    Set mergedrange = cll.MergeArea
    mergedrange.UnMerge
    For Each celle In mergedrange.Cells
      If IsEmpty(celle.Value) Then
        celle.Value = myValue
        celle.NumberFormat = myFormat
      Else
        myValue = celle.Value
        myFormat = celle.NumberFormat
      End If
    Next celle
  End If
Next cll
End Sub

Note that this puts a 'hard' value (not a formula) in all but the first cell in merged areas.