Get data from split/merged cells

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.