This Excel tutorial explains how to use Excel VBA to reformat merged cells to row.
You may also want to read:
Fill Blank Rows or blank Cells
Seperate line break data into different rows
Excel VBA unmerge columns automatically
Excel VBA reformat merged cells to row
It is difficult to decide the title of this post, I will illustrate with an example.
Imagine a Table with column A being a merged cell, and column B are the items under the same group.
When you apply filter on column A, you are only allowed to display “Group A”, while in Column B, you can only see “Item 1”, the reason is that the value “Group A” lies on the top of the merged Cell (A1), the value of A2 and A3 are blank.
Now it is a good idea to unmerge column A and then move B2 and B3 values to B1, so that you can do filtering.
his Excel tutorial explains how to use Excel VBA to reformat merged cells to row.
Excel VBA reformat merged cells to row
The following procedure assumes
- Column A contain target merged cell to be unmerged
- Column B contains data to group together
Public Sub unmerge()
If Range("A1") = "" Then Exit Sub
Application.ScreenUpdating = False
A = ActiveSheet.UsedRange.Rows.Count
LastRow = Range("B" & Rows.Count).End(xlUp).Row
usedRangeLastColNum = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
Do While Application.WorksheetFunction.CountBlank(Range("A1", "A" & LastRow)) > 0
For r = LastRow To 1 Step -1
If Range("A" & r) = "" Then
For c = 2 To usedRangeLastColNum
If Cells(r, c).Value <> "" Then
Cells(r - 1, c).Value = Cells(r - 1, c).Value & vbLf & Cells(r, c)
End If
Next c
Cells(r, c).EntireRow.Delete
End If
Next r
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Loop
Application.ScreenUpdating = True
dummy = MsgBox("Conversion completed", vbInformation)
Range("A1").Select
End Sub
Example – reformat merged cells to row
For example, you have the below data
Run the Macro and you will get the followings.
