How To Unmerge Cells And Duplicate Data In Excel Using VBA
When you have merged cells in Excel, it can be difficult to process the data because only the first cell contains a value and the rest of the merged range is blank.
In this video, we look at how you can unmerge all the cells in a specified range and then duplicate the data over the cells in their respective merged ranges.
The code is as follows:
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Sheet1")
Dim cell As Range
Dim joinedCells As Range
For Each cell In ws.Range(Cells(1, 1), Cells(2, 10))
If cell.MergeCells Then
Set joinedCells = cell.MergeArea
cell.MergeCells = False
joinedCells.Value = cell.Value
End If
Next