This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column.
You may also want to read:
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
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.