No.187 ~ VBA: Find Merged Cells, Then UnMerge Them! ~ 2017-10-04 09:01:42
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.
#coding #data #development #excel #merged cells #ms office #vba
No.186 ~ don't worry love, we'll try again later ~ 2017-09-27 10:10:06
#music #reckonwrong #the passions of pez #whities 009
No.184 ~ i never wanted it to end ~ 2017-09-19 10:49:45
#music #only 4 u #rivergazer