How to unmerge cells while merging the data (if any) on the two cells

[Shaan Bhasin]

Hi,

I've cells on two rows which are merged, however, some cells are not merged and have different values/data. E.g. I want to bring Row 14-15 in one row also and delete the empty line and merge the comment section.

While I import the table in power query, it shows one row as nil value, so I want to cleanse the data before importing, Is there any smart solution either through VBA or power query, rather doing it manually?

Thanks in advance for your help.

 

[Anthony47]

Maybe using a macro. For example:

Sub UnMerge()
'Ref: D:\DDownloads\[MULTI_C30923.xlsm]Foglio1
Dim SelAr As Range, nwTxt As String
Dim I As Long, J As Long, K As Long, mRC As Long
'
If Selection.Count < 5 Then
    MsgBox ("Select the area to be checked, then retry")
    Exit Sub
End If
ActiveSheet.Copy after:=ActiveSheet
Set SelAr = Selection
For I = SelAr.Rows.Count To 1 Step -1
    mRC = SelAr.Cells(I, 1).MergeArea.Rows.Count - 1
    If mRC > 0 Then
        For J = 1 To SelAr.Columns.Count
            If SelAr.Cells(I - mRC, J).MergeArea.Count > 1 Then
                SelAr.Cells(I - mRC, J).UnMerge
            Else
                nwTxt = ""
                For K = 0 To mRC
                    If SelAr.Cells(I - mRC + K, J) <> "" Then
                        nwTxt = nwTxt & " " & SelAr.Cells(I - mRC + K, J).Value
                    End If
                Next K
                Debug.Print SelAr.Cells(I - mRC, J).Resize(mRC + 1, 1).Address(0, 0), SelAr.Cells(I - mRC, J).Address(0, 0)
                SelAr.Cells(I - mRC, J).Resize(mRC + 1, 1).ClearContents
                SelAr.Cells(I - mRC, J).Value = Mid(nwTxt, 2)
            End If
        Next J
        Debug.Print SelAr.Cells(I - mRC + 1, J).Resize(mRC).Address(0, 0)
        SelAr.Cells(I - mRC + 1, J).Resize(mRC).EntireRow.Delete
        I = I - mRC
    End If
Next I
SelAr.Cells(1, 1).Select
MsgBox ("Completed, double-check the output")
End Sub

Copy the code into a vba Standard Module.
Then SELECT the area to rework and run Sub UnMerge:
-a copy of the original worksheet will be created, and the process to unmerge rows and merge text will be executed
-you need to check that the output is correct against the input

Be aware that the code doesn't deal with merged columns
See the image for the results of my test

Try...