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 reformat merged cells to row

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.

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *