Unmerging excel rows, and duplicate data
You don't need VBA for something like that.
- Select the range of the merged cells
- Unmerge the cells
- Home -> Find and Select -> Go to special... -> Blanks -> ok
- Type "=" move one cell up and press Ctrl + Enter
If R1C1 styles are enabled from: File -> Options -> Formula -> R1C1 Reference style then
- ...
- ...
- ...
- Type "=R[-1]c" and press Ctrl + Enter
This is a VBA solution. This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range (what the value was).
Sub UnMergeFill()
Dim cell As Range, joinedCells As Range
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
If cell.MergeCells Then
Set joinedCells = cell.MergeArea
cell.MergeCells = False
joinedCells.Value = cell.Value
End If
Next
End Sub
This is based on @aevanko 's answer, but generalized to an arbitrary Range.
Sub UnMergeRange(WorkRng As Range)
Dim cell As Range, joinedCells As Range
For Each cell In WorkRng
If cell.MergeCells Then
Set joinedCells = cell.MergeArea
cell.MergeCells = False
joinedCells.Value = joinedCells.Cells(1, 1).Value
End If
Next
End Sub
I also made a change because I came upon a corner case where it didn't work. It's when there's a vertically merged cell that includes cells above the provided range. I had to use a slightly more advanced expression to get the value.
- Select the range which has merged data
- Click on Merge and Centre to unmerge cells
- Select the range of data again
- Press Ctrl+G > Special > Blanks
- Press = and up arrow key
- Press Ctrl+Enter