CODEWITHSUNDEEP
vbaexcel
H3katonkheir
H3katonkheir

Reputation: 663

Unmerging excel rows, and duplicate data

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.

Upvotes: 37

Views: 82170

Answers (6)

pgr
pgr

Reputation: 928

This is based on @aevanko 's answer, but generalized to an arbitrary Range.

Sub UnMergeRange(WorkRng As Range)
    Dim cell As Range, joinedCells As Range
   
    For Each cell In WorkRng
        If cell.MergeCells Then
            Set joinedCells = cell.MergeArea
            cell.MergeCells = False
            joinedCells.Value = joinedCells.Cells(1, 1).Value
        End If
    Next
End Sub

I also made a change because I came upon a corner case where it didn't work. It's when there's a vertically merged cell that includes cells above the provided range. I had to use a slightly more advanced expression to get the value.

Upvotes: 1

Jozef Bulat
Jozef Bulat

Reputation: 1

here is another way to have data in all cells. I have a report that is published daily with merged cells, to avoid daily unmerging cells I refer to report with Power Query.

using Fill down option I can populate data to all cells in the merged range. Works for me! :)

Report view with marked FIll Down option

Upvotes: 0

David Martin
David Martin

Reputation: 1

I don't know how to quickly implement the VBA code, but I used a formula to populate another column with the values... then I did the copy/paste magic of pasting values over the unmerged cells. This formula is based on the fact that the value is associated with the FIRST cell of the merged cells, and all others resolve to 0 (zero).

If Col. A2:A100 has the merged cells, I did the following:

  1. In Cell B2, I entered this formula: =IF(A2<>0,A2, B1)
  2. Copy the formula down to B100
  3. Copy/Paste VALUES ONLY to column C.
  4. Unmerge all of Column A.
  5. Copy Values from Column C into Column A.

Actually, you can simplify it to this series if you're bold...

  1. Unmerge all of Column A
  2. In Cell B2, I entered this formula: =IF(A2<>0,A2, B1)
  3. Copy the formula down to B100
  4. Copy/Paste VALUES ONLY to column A

That skips the use of Column C...

Upvotes: 0

Montells
Montells

Reputation: 6679

  1. Select the range which has merged data
  2. Click on Merge and Centre to unmerge cells
  3. Select the range of data again
  4. Press Ctrl+G > Special > Blanks
  5. Press = and up arrow key
  6. Press Ctrl+Enter

Upvotes: 20

thanos.a
thanos.a

Reputation: 2694

You don't need VBA for something like that.

  1. Select the range of the merged cells
  2. Unmerge the cells
  3. Home -> Find and Select -> Go to special... -> Blanks -> ok
  4. Type "=" move one cell up and press Ctrl + Enter

If R1C1 styles are enabled from: File -> Options -> Formula -> R1C1 Reference style then

  1. ...
  2. ...
  3. ...
  4. Type "=R[-1]c" and press Ctrl + Enter

Upvotes: 3

Gaijinhunter
Gaijinhunter

Reputation: 14685

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

Upvotes: 66

Related Questions