Menu
I have been given a fairly large database stored in Microsoft Excel, which I have to try convert into something useful.
However, one of the problems that I am encountering is that some of the data is merged together (horizontally in 2s).
For example;
row 1: [ x ][ x ][ x ][ x ][ x ] row 2: [ x ][ x ][ o o ][ x ] row 3: [ o o ][ x ][ o o ] Where x's are single cells and o's are merged together
What I want to do is unmerge all the rows (which I could do fairly easy with the unmerge button), but for where the merged cell was, have the data duplicated across the 2 cells.
From; [[ Some Data ]]
To; [ Some Data ][ Some Data ]
Thanks! Any help is appreciated.
206
Select the entire worksheet. For this, either click the little triangle in the upper-left corner of the worksheet or press the Ctrl + A shortcut. With all the cells in the sheet selected, have a look at the Merge & Center button: If it is highlighted, click it to unmerge all merged cells in the worksheet.
To unmerge cells immediately after merging them, press Ctrl + Z. Otherwise do this: Click the merged cell and click Home > Merge & Center. The data in the merged cell moves to the left cell when the cells split.
Select and press Ctrl +C to copy the merged cells that you want to paste to other single cells.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
