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 "-". Not sure if it makes a difference, but the data in column B is always going to be 3 characters in length.

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!

 

[Loomah]

tdpman
Double posting? Can't complain here though as this is actually the correct forum!!!

See your thread in the office forum for suggestions.
;-)

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

 

[TonyJollans]

Hi tdpman,

If your data in column B is always 3 characters then splitting the text is easy - modifying your merge routine to become an unmerge gives ..

Sub

Un

mergecells()
   Dim intRow As Integer
   Dim txt As String
   intRow = 2
   Do Until IsEmpty(Cells(intRow, 2))
      Range(Cells(intRow, 2), Cells(intRow, 3)).

Un

Merge

      Cells(intRow, 3) = Mid$(Cells(intRow, 2),7)
      Cells(intRow, 2) = Left$(Cells(intRow, 2),3)

      intRow = intRow + 1
   Loop
   Columns(

"B:C"

).AutoFit
End Sub

BUT you are making things very difficult by doing the whole thing this way. Why not put your temporary concatenation in a new column. In D1 put =B1&" - "&C1 and than copy it down as far as your data goes, and when you're done just delete column D.

Enjoy,
Tony

 

[TonyJollans]

Hi tdpman,

Just saw Loomah's post after I did mine and went and checked the Office forum - seems you might as well ignore me here, you have all your answers there.

Enjoy,
Tony

 

[tdpman]

Thanks everyone, I posted over there and then figured out that this was actually the correct forum. Sorry about that one. You guys are awesome!