Random GIF that is link to Statistics Page

Popular Tags

Last 30 Days Last 3 Months Last 6 Months All Time Random

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
    Set rng = Selection
    For Each cell In rng
        cell.Value = val
    Next cell
    '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
        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