Split data

[Deepk]

Hi all,

I want a macro for changing the following data

Input:
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]AB2345
SD7890[/TD]
[TD="class: xl65, width: 64"]AB
XC[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CB2345
GH6543[/TD]
[TD="class: xl65, width: 64"]SD
FG[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]ER2345
CV3455[/TD]
[TD="class: xl65, width: 64"]GH
MN[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WX9876
RE3456[/TD]
[TD="class: xl65, width: 64"]HJ
KL[/TD]
[/TR]
</tbody>[/TABLE]

Output:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]AB2345[/TD]
[TD="class: xl65, width: 64"]AB[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]SD7890[/TD]
[TD="class: xl65, width: 64"]XC[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CB2345[/TD]
[TD="class: xl65, width: 64"]SD[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]GH6543[/TD]
[TD="class: xl65, width: 64"]FG[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]ER2345[/TD]
[TD="class: xl65, width: 64"]GH[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CV3455[/TD]
[TD="class: xl65, width: 64"]MN[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WX9876[/TD]
[TD="class: xl65, width: 64"]HJ[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]RE3456[/TD]
[TD="class: xl66, width: 64"]KL[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[Rick Rothstein]

You are probably going to use Peter's new code in Message #20 , but, for the record, here is my code modified to handle your new data structure. Note that I have assumed the gaps you show between the groups of values is to delineate one cell's values from another and does not represent a blank cell on the worksheet between them.

Sub Split_Data()
  Dim LastRow As Long, ArrA As Variant, ArrB As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  ArrA = Application.Transpose(Split(Join(Application.Transpose(Range("A1:A" & LastRow)), vbLf), vbLf))
  ArrB = Application.Transpose(Split(Join(Application.Transpose(Range("B1:B" & LastRow)), vbLf), vbLf))
  Range("C1").Resize(UBound(ArrA)) = ArrA
  Range("D1").Resize(UBound(ArrB)) = ArrB
End Sub

 

[Deepk]

You are probably going to use Peter's new code in Message #20 , but, for the record, here is my code modified to handle your new data structure. Note that I have assumed the gaps you show between the groups of values is to delineate one cell's values from another and does not represent a blank cell on the worksheet between them.

Thank you Rick and Peter. you people are are awesome.

 

[Peter_SSs]

Cheers. Good luck with the project!