Excel 2010 VB Help - Split Cell Data to +Rows

JGEA

New Member
Joined
Nov 3, 2011
Messages
4
Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Background<o:p></o:p>
We have lengthy documentation in excel 2010 which has some data wrapped (Alt-Enter) in a cell.<o:p></o:p>
<o:p> </o:p>
Goal<o:p></o:p>
To identify and separate each element (or line) or text in these cells and place them into new rows automatically. The trick is that new rows need to be dynamically added to make room for each line of text otherwise previous codes samples simply overwrite the following columns.<o:p></o:p>
<o:p> </o:p>
What We Have<o:p></o:p>
Currently I have two pieces of code which do their job well but we need to put them together and iterate the entire worksheet for each row with data in it. The “SplitCellDataByLines” code correctly identifies the data split within the columns and iterates through the entire sheet correctly BUT outputs to the columns adjacent. The “BlankRows” code iterates through the sheet but only adds a single row to each which isn’t useful since some rows needs 25 additions and others do not need any additions.<o:p></o:p>
Sub SplitCellDataByLines()<o:p></o:p>
Dim c As Range, SP, a As Long<o:p></o:p>
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))<o:p></o:p>
SP = Split(c, Chr(10))<o:p></o:p>
For a = LBound(SP) To UBound(SP)<o:p></o:p>
Cells(c.Row, a + 1) = SP(a)<o:p></o:p>
Next a<o:p></o:p>
Next c<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
Sub BlankRows()<o:p></o:p>
'Select last row in worksheet.<o:p></o:p>
Selection.End(xlDown).Select<o:p></o:p>
Do Until ActiveCell.Row = 1<o:p></o:p>
'Insert blank row.<o:p></o:p>
ActiveCell.EntireRow.Insert shift:=xlDown<o:p></o:p>
'Move up one row.<o:p></o:p>
ActiveCell.Offset(-1, 0).Select<o:p></o:p>
Loop<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
Ideally I’m looking to make this into a single process to reduce human error if/ when other people use it; but I also realize it may just be easier to have a process to count the number of lines in a cell + add same number of rows and another process to split the data out to these rows after.<o:p></o:p>
<o:p> </o:p>
Sample Data<o:p></o:p>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 564pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=752><TBODY><TR style="HEIGHT: 63.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 63.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323>Column B<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 63.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429>Column C (target for processing)<o:p></o:p>
</TD></TR><TR style="HEIGHT: 90.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323>Directory1,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429> Edward Lifshitz,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Brian Karnofsky,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Sonny Gutkind,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Kenneth Smith,OU=Users,OU=SBS <o:p></o:p>

</TD></TR><TR style="HEIGHT: 90.75pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323> Directory2,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429> Sever Hida,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Sol Zimmerman,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Derrick Wong,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Efleda Almadin,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Connie Hu,OU=Users,OU=SBS
Ying Yu,OU=Users,OU=SBS <o:p></o:p>

</TD></TR></TBODY></TABLE><o:p> </o:p>
<o:p> </o:p>
Thanks again for any help and insights into our dilemma
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
Sub SplitData()
Dim LR As Long, i As Long, X
Columns("B").Insert
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    X = Split(Range("A" & i).Value, Chr(10))
    Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(X) + 1).Value = Application.Transpose(X)
Next i
Columns("A").Delete
End Sub
 

JGEA

New Member
Joined
Nov 3, 2011
Messages
4
Hey VoG

The code you provided output a shift in the column A cells down one row. I realize this could be confusing so below is an example of what the new formating should look like again the sample I provided above.

NOTE: I changed the column heading in the smaple, my mistake


Sample Data<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 564pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=752><TBODY><TR style="HEIGHT: 63.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 63.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323>Column A<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 63.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429>Column B (target for processing)<o:p></o:p>
</TD></TR><TR style="HEIGHT: 90.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323>Directory1,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429> Edward Lifshitz,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Brian Karnofsky,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Sonny Gutkind,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Kenneth Smith,OU=Users,OU=SBS <o:p></o:p>

</TD></TR><TR style="HEIGHT: 90.75pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 242pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=323> Directory2,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 322pt; PADDING-RIGHT: 5.4pt; HEIGHT: 90.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=429> Sever Hida,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Sol Zimmerman,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Derrick Wong,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Efleda Almadin,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest
Connie Hu,OU=Users,OU=SBS
Ying Yu,OU=Users,OU=SBS <o:p></o:p>

</TD></TR></TBODY></TABLE>
<o:p> </o:p>
<o:p> </o:p>
Final Output
**Note how the sample data has multiple elements wrapped in a single cell and new data has individual elements in individual cells**<o:p></o:p>
<o:p> </o:p>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 715pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=953><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap>Column A<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634 noWrap>Column B (target for processing)<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319> Directory1,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Edward Lifshitz,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Brian Karnofsky,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Sonny Gutkind,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Kenneth Smith,OU=Users,OU=SBS <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319> Directory2,OU=Groups,OU=SBS <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Sever Hida,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Sol Zimmerman,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Derrick Wong,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Efleda Almadin,OU=Users,OU=NYCO,OU=Amper,OU=Amper Forest <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Connie Hu,OU=Users,OU=SBS <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 239.25pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=319 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 475.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=634> Ying Yu,OU=Users,OU=SBS <o:p></o:p>
</TD></TR></TBODY></TABLE>

Thanks again for your quick response
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If the values to be split are in column B try

Code:
Sub SplitData()
Dim LR As Long, i As Long, X
Columns("C").Insert
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    X = Split(Range("B" & i).Value, Chr(10))
    Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(X) + 1).Value = Application.Transpose(X)
Next i
Columns("B").Delete
Range("B1").Delete shift:=xlShiftUp
End Sub
 

JGEA

New Member
Joined
Nov 3, 2011
Messages
4

ADVERTISEMENT

Hey Vog,

The code bombs out half way through. It prints the data to the C column in the order as seen within the original B column but does not yet add any rows or place the data back in order with its respective A column (the relationship is less important since it can be related again processing fiarly easily).

The debugger haults at line 7 - Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(X) + 1).Value = Application.Transpose(X)

Thanks again for your attention
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hmm, well it works foe me. If you use your original code then you need to loop backwards.
 

JGEA

New Member
Joined
Nov 3, 2011
Messages
4
OK I'll look closer at it, thanks you've given me some ideas.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,991
Messages
5,599,235
Members
414,297
Latest member
dalkarl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top