Hi all,
I currently have a solution for what I think is a fairly common problem, but because it loops, it takes forever.
Is there a single operation way of doing this? it also only works for merges vertically, ideally we want both ways.
Find all merged cells, unmerge them, and fill each range with the data that was merged and now only occupies top left.
I've found that you can search for merged cells with find and replace, but it outputs a list of them, within the find dialog, I can't find a way to make them selected.
There are other blank cells on the sheet which need to remain blank, some of which may be adjacent to merged cells, so I can't just unmerge then 'find blank' and make them all =R-1C.
Edit: I've tried findblank to make all prexisting blanks read something like "blank" which (after the demerge+fill) I would then find and replace with actual blanks, but it inputs "blank" in all the merged cells too, even over-writing the data in those merged cells.
Current
Sub Demerge()
LastRow = Range("H10000").End(xlUp).Row
myColumn = 1
Do Until myColumn = 26
myRow = 7
Do Until myRow = LastRow
Cells(myRow, myColumn).Select
If Cells(myRow, myColumn).MergeCells = True Then
Cells(myRow, myColumn).UnMerge
Selection.FillDown
Else
End If
myRow = myRow + 1
Loop
myColumn = myColumn + 1
Loop
End Sub
This only works for vertically merged only cells, which is fine for this version, and if we can't get a solution that does mixed vertical only merges, horizontal only merges and both-ways merges, then we will give up and go with a vertical only solution, but long term we're probably going to have to come back to it for something universal.
Further edit: more googling brought me this, seems to solve the horizontal/vertical issue, but it still loops. Was really hoping for something like find and replace that would select just the merged cells and do them all in one go. Don't understand HOW this one works though...
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
Bookmarks