Excel VBA unmerge columns automatically

This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column.

You may also want to read:

Excel VBA separate line break data into different rows

Excel VBA unmerge columns automatically

For some systems, when you export a non-Excel report (e.g. PDF) to Excel format, some columns could be merged in order to fit the width of the original report layout. Even for Sharepoint, this formatting issue also happens.

In order to address this issue, I have created a Macro to unmerge those merged columns.

VBA Code -unmerge columns automatically

Press ALT+F11, copy and paste the below Procedures to a new Module.

Public Sub unmerge()
    If ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).MergeCells = True Then
        ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).unmerge
    End If
    
    usedRangeLastColNum = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

    For c = usedRangeLastColNum To 1 Step -1
        If Cells(1, c).Value = "" Then
            Columns(c).EntireColumn.Delete
        End If
    Next
End Sub

Explanation:

This Macro loops through the row 1 of right column to left column. If the Cell value is blanked, then delete the whole column.

Take field C as example.

Cell C1, D1 and E1 are merged. For merged cells, the Cell value lies in the first Cell on the left. Therefore C1 value is “Field C”, but D1 and E1 are blank.

When looping through range E1 to C1, column D and E are deleted because they contain blank value, leaving only column C.

For column G to J is a little tricky. The last used cell is G12, not J12, so I cannot delete column H to J. As a workaround, I check whether column H12 is merged, then unmerge it so that I can loop from J1.

Example -unmerge columns automatically

Run the Macro to get the below result.