Excel "Unmerge" Macro???

[tdpman]

OK, guys and gals, I need a little assistance. I have a spreadsheet containing a macro that will merge columns B & C into one column and place an "-" between the data. It is as follows:

***************

Sub mergecells()
Dim intRow As Integer
Dim txt As String
intRow = 2
Do Until IsEmpty(Cells(intRow, 2))
Cells(intRow, 2) = Cells(intRow, 2) & " - " & Cells(intRow, 3)
Cells(intRow, 3).ClearContents
Range(Cells(intRow, 2), Cells(intRow, 3)).Merge
intRow = intRow + 1
Loop
Columns(2).AutoFit
End Sub

***********************

Now what I need is a macro that will undo this, and place the data back into columns B & C as it was before and remove the "-".

Basically what I am doing in this spreadsheet is combining columns B & C, running a dup check on it, and returning the data back to the columns like it was.

Any ideas? Thanks for your help on this! All of you have helped me tremendously in the past and it has been most appreciated!!! Thanks again!

 

[tdpman]

If it makes any difference, the data in column B is always going to be 3 characters. Not sure if that makes a diff or not.

 

[Zathras]

Why are you merging cells if all you really need to do is merge (really concatenate) the data?

Either re-write your dup check routine to work with data from two cells, or simply insert a temporary column and put your concatenated text there, do the dup check and then delete the temporary column.

 

[luceze]

Will the text to columns function not work?

 

[Loomah]

Hi tdpman
Zathras has an excellent point there - again!

However, I wasn't thinking about it so here's some code anyway! It doesn't matter about the length of each string you're "merging" but does rely on the separator being " - " ie with spaces.

Sub UnMerge()
Dim lRow As Long
Dim iMidChar As Integer
For lRow = 2 To Range("B65536").End(xlUp).Row
    Cells(lRow, 2).mergecells = False
    iMidChar = InStr(1, Cells(lRow, 2).Value, "-")
    Cells(lRow, 3) = Right(Cells(lRow, 2), Len(Cells(lRow, 2)) - (iMidChar + 1))
    Cells(lRow, 2) = Left(Cells(lRow, 2), iMidChar - 2)
Next
End Sub

But I would now see this code as an academic exercise and take Zathras' advice.
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[tdpman]

You all are awesome, that works wonderfully!!! I know I'm probably making this more complicated than it needs to be, but I still appreciate the help!