Forgive the boring nature of the first technical post in a long time - I'm testing my formatting of code in blog posts.
This is a chunk of VBA I wrote to solve a problem in Excel which I don't know if there's a native function to solve: when you try to sort a table with merged cells, it will return an error. If you have a large table, then you have to find these merged cells and unmerge them individually. Unfortunately the newly unmerged cells don't share the same value: all but the top-left cell are left blank.
So I wrote the following two subs:
Sub merge_fill()
'
' merge_fill Macro
' highlight merged cell. split into individual cells and duplicate contents across active range
'
' Keyboard Shortcut: Ctrl+Shift+M
'
'define vars
Dim val As String, rng As Range, cell As Range
'start by asking whether the cell is merged
If ActiveCell.MergeCells Then
'if merged area is actually merged then grab value and unmerge
val = ActiveCell.Value
ActiveCell.MergeArea.UnMerge
Set rng = Selection
For Each cell In rng
cell.Value = val
Next cell
Else
'if it's not hen just exit with a message
MsgBox "not merged"
End If
End Sub
Sub findMergedAndFill()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
'start by asking whether the cell is merged
If cell.MergeCells Then
cell.Select
MsgBox "Merged! About to unmerge"
Call merge_fill
End If
Next cell
End Sub
The sub "merge_fill" unmerges a selected cell and copies the origional value of the merged cell in all child cells
The sub "findMergedAndFill" loops through a Selection of cells, checks if any cells are merged, then selects any merged cells and calls the "merge_fill" sub, and announces it on each occasion.