VBA - Merged columns

[ChrisFoster]

Hi all,

I have a number of reports that are created by my CRM system. Some of the reports were built by a developer who liked to merge data across numerous columns - for instance column A:B = First Name, C:D = Surname.

Each report could have merged data across various different columns, so it's not always consistent.

The data I need is always stored in the left hand column, so where column A:B = First Name - I could just delete column B and this unmerges the cells and leaves me with the data I need but now just in one column, which would be A.

Is there any VBA code that I can use to find and delete the blank columns within a merged range? My thinking is I could maybe use code to unmerge the whole worksheet, then delete any blank columns - But I'm unsure of the best way forward. Any help would be appreciated.

Regards,

Chris

 

[gallen]

Would be something like this:

    If Range("A1").MergeCells = True Then
        Range("B1").EntireColumn.Delete
    End If

Always test on a copy of data

 

[ChrisFoster]

Would be something like this:

    If Range("A1").MergeCells = True Then
        Range("B1").EntireColumn.Delete
    End If

Always test on a copy of data

This will work if the merged columns are always A:B - but the merged data across various different columns, so it's not always consistent.

 

[footoo]

Maybe :

Sub v()
Dim cols%, c%
Cells.MergeCells = False
cols = Range([A1], ActiveSheet.UsedRange).Columns.Count
For c = cols To 1 Step -1
    If Application.CountA(Columns(c)) = 0 Then Columns(c).Delete
Next
End Sub

 

[gallen]

So would you like to check all the way across row 1 and check if it is merged, and if so delete the adjacent row?

 

[ChrisFoster]

Maybe :

Sub v()
Dim cols%, c%
Cells.MergeCells = False
cols = Range([A1], ActiveSheet.UsedRange).Columns.Count
For c = cols To 1 Step -1
    If Application.CountA(Columns(c)) = 0 Then Columns(c).Delete
Next
End Sub

Works perfectly, thank-you.