VBA - Merged columns

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
Would be something like this:

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

Always test on a copy of data
 

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
Would be something like this:

VBA Code:
    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

Well-known Member
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
Maybe :
VBA Code:
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

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
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

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
Maybe :
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,230
Messages
5,546,641
Members
410,751
Latest member
Mike Davis 1977
Top