Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Background<o></o>
We have lengthy documentation in excel 2010 which has some data wrapped (Alt-Enter) in a cell.<o></o>
<o> </o>
Goal<o></o>
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></o>
<o> </o>
What We Have<o></o>
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></o>
Sub SplitCellDataByLines()<o></o>
Dim c As Range, SP, a As Long<o></o>
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))<o></o>
SP = Split(c, Chr(10))<o></o>
For a = LBound(SP) To UBound(SP)<o></o>
Cells(c.Row, a + 1) = SP(a)<o></o>
Next a<o></o>
Next c<o></o>
End Sub<o></o>
<o> </o>
Sub BlankRows()<o></o>
'Select last row in worksheet.<o></o>
Selection.End(xlDown).Select<o></o>
Do Until ActiveCell.Row = 1<o></o>
'Insert blank row.<o></o>
ActiveCell.EntireRow.Insert shift:=xlDown<o></o>
'Move up one row.<o></o>
ActiveCell.Offset(-1, 0).Select<o></o>
Loop<o></o>
End Sub<o></o>
<o> </o>
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></o>
<o> </o>
Sample Data<o></o>
<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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</TD></TR></TBODY></TABLE><o> </o>
<o> </o>
Thanks again for any help and insights into our dilemma
<o> </o>
Background<o></o>
We have lengthy documentation in excel 2010 which has some data wrapped (Alt-Enter) in a cell.<o></o>
<o> </o>
Goal<o></o>
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></o>
<o> </o>
What We Have<o></o>
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></o>
Sub SplitCellDataByLines()<o></o>
Dim c As Range, SP, a As Long<o></o>
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))<o></o>
SP = Split(c, Chr(10))<o></o>
For a = LBound(SP) To UBound(SP)<o></o>
Cells(c.Row, a + 1) = SP(a)<o></o>
Next a<o></o>
Next c<o></o>
End Sub<o></o>
<o> </o>
Sub BlankRows()<o></o>
'Select last row in worksheet.<o></o>
Selection.End(xlDown).Select<o></o>
Do Until ActiveCell.Row = 1<o></o>
'Insert blank row.<o></o>
ActiveCell.EntireRow.Insert shift:=xlDown<o></o>
'Move up one row.<o></o>
ActiveCell.Offset(-1, 0).Select<o></o>
Loop<o></o>
End Sub<o></o>
<o> </o>
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></o>
<o> </o>
Sample Data<o></o>
<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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</TD></TR></TBODY></TABLE><o> </o>
<o> </o>
Thanks again for any help and insights into our dilemma