Excel 2010 VB Help - Split Cell Data to +Rows

J

JGEA

New Member
Joined
Nov 3, 2011
Messages
4
Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><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 Lif****z,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
 
Sort by date Sort by votes
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
 
Upvote 0
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:office:office" /><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 Lif****z,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 Lif****z,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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hmm, well it works foe me. If you use your original code then you need to loop backwards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
907
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
1K
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Share this page

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
Back
Top