Checking and removing merged cells

[mps80y]

Hi,

I have a spreadsheet that for some unknown reason certain users love merging cells. We're in the process of stamping out that practice, but in the meantime as part of an import and VLOOKUP I need to be able to removed the merged calls.

The cells are G and H on the sheet and contain suburbs eg: [G: CENTRAL - LEICHHARDT -] [H: MERRYLANDS]

I am able to use VBA to identify the cells to the user but the process takes awhile to run. Is there any way to simply run a process so that it identifies a merged cell, unmerges it and replaces the values as below

[G: CENTRAL via LEICHHARDT] [H: MERRYLANDS]

(obviously the letter at the start represents the cell the value is in).

I'm new to Excel VBA so would appreciate any support.

Cheers

 

[jkpieterse]

Re: Help checking and removing merged cells

What is in the merged cells precisely?

 

[mps80y]

Column G is 'Pick up Address' and Column H is 'Drop off Address'

The values in Cells G & H are merged for some rows and the content is always in the format 'CITY - CITY2 - CITY3'. Which means we picked up clients from City1 and dropped them at City3 via CITY2.

The problem is when I try to use the VLOOKUP function to retrieve data from columns G and H if they're merged I can't retrieve the value.

In the attached screenshot, lines 506 & 508 are correct, but I need a script to make 507 say something like 'Cell G: Lidcombe via Bankstown' and 'Cell H: Lidcombe'.

http://imgur.com/a/MVxOgbB

I hope this makes it somewhat clearer.

 

[jkpieterse]

Got it. Can you upload some sample data with at least one merged cell somewhere and post a link here so I have something to go on?

 

[mps80y]

Got it. Can you upload some sample data with at least one merged cell somewhere and post a link here so I have something to go on?

Thanks for your help. Here is a link to a sample sheet. The original sheet can have up to 5000 rows.

https://drive.google.com/open?id=1gPLaVD3_HvrjbFgXcbncWrWEOGDoUIeL

 

[jkpieterse]

This macro should do it:

Sub Unmerge()
    Dim oCell As Range
    Dim sVal As String
    For Each oCell In Intersect(ActiveSheet.UsedRange, Range("G:G"))
        If oCell.MergeCells Then
            sVal = oCell.Value
            oCell.Unmerge
            oCell.Value = Replace(Trim(Left(sVal, InStrRev(sVal, "-") - 1)), " - ", " via ")
            oCell.Offset(, 1).Value = Trim(Mid(sVal, InStrRev(sVal, "-") + 1, Len(sVal)))
        End If
    Next
End Sub

 

[mps80y]

This worked perfectly! Thank you!!!