Ummerge then fill all cells with contents of merged cell

[caj1980]

I need a script (macro) that will take all merged cells in a worksheet, unmerge them, then fill them with the text that was in the original merged cell. Multiple unique instances throughout the worksheet. Prefer a solution that would repeat this function for all merged cells within the worksheet.

Sanitized version of my spreadsheet is here:
Dropbox - Public

 

[Rick Rothstein]

I need a script (macro) that will take all merged cells in a worksheet, unmerge them, then fill them with the text that was in the original merged cell.

I believe this macro will do what you are asking for...

Sub UnmergeAndFill()
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .UnMerge
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub

 

[JoeMo]

I don't download so haven't seen your file so try this on a copy of your worksheet.

Sub FindMergedCells()
Dim c As Range, Rws As Long, Cols As Long, mA() As String, ct As Long
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    If c.MergeCells Then
        Rws = c.MergeArea.Rows.Count
        Cols = c.MergeArea.Columns.Count
        With c
              .UnMerge
              .Resize(Rws, Cols).Value = .Value
       End With
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[caj1980]

I don't download so haven't seen your file so try this on a copy of your worksheet.

Sub FindMergedCells()
Dim c As Range, Rws As Long, Cols As Long, mA() As String, ct As Long
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    If c.MergeCells Then
        Rws = c.MergeArea.Rows.Count
        Cols = c.MergeArea.Columns.Count
        With c
              .UnMerge
              .Resize(Rws, Cols).Value = .Value
       End With
    End If
Next c
Application.ScreenUpdating = True
End Sub

This worked great! Thanks, @JoeMo

 

[JoeMo]

You are welcome. Did you try Rick's code? If it works, it should be faster than mine.