maro, insert rows where rows determined by number of carriage returns

A

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
The best way to explain my problem is to look at the table below:

<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=464><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" span=4 width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=116>How it looks now: </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD></TR><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 47.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=63>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 1
Price 2
Price 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 45.75pt; mso-height-source: userset" height=61><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=61>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 1
Store 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Very Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=42>How I want it to look:</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Very Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Very Delicious</TD></TR></TBODY></TABLE>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Here is a start for you:

len(Activecell.Text)-len(replace(activecell.Text,vblf,""))+1

That will tell you how many lines you will need to use for the activecell.
 
Upvote 0
Thanks Bladehunter, appreciate the fast reply. So i actually looked at another post by crazyfrog where Joe4 replied with a code suggestion:

Sub MySplit()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
Dim myLastRow As Long<o:p></o:p>
Dim myCol As Long<o:p></o:p>
Dim myLen As Long<o:p></o:p>
Dim myString As String<o:p></o:p>
Dim myStart As Long<o:p></o:p>
Dim myEnd As Long<o:p></o:p>
Dim i As Long<o:p></o:p>
Dim j As Long<o:p></o:p>
<o:p></o:p>
' Copy data from sheet1 to sheet2<o:p></o:p>
Sheets("Sheet1").Select<o:p></o:p>
Cells.Copy<o:p></o:p>
Sheets("Sheet2").Select<o:p></o:p>
Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Application.CutCopyMode = False<o:p></o:p>
<o:p></o:p>
' Set column with line feeds ("B"=2)<o:p></o:p>
myCol = 2<o:p></o:p>
<o:p></o:p>
' Find last row of data in specified column<o:p></o:p>
myLastRow = ActiveSheet.Cells(Rows.Count, myCol).End(xlUp).Row<o:p></o:p>
<o:p></o:p>
For i = myLastRow To 1 Step -1 'Loop through all columns<o:p></o:p>
myLen = Len(Cells(i, myCol)) 'Get length of entry<o:p></o:p>
If myLen > 0 Then 'If lenghth>0, capture string and loop through characters<o:p></o:p>
myString = Cells(i, myCol)<o:p></o:p>
myEnd = myLen<o:p></o:p>
For j = myLen To 1 Step -1<o:p></o:p>
If Asc(Mid(myString, j, 1)) = 10 Then 'Look for line breaks<o:p></o:p>
myStart = j + 1<o:p></o:p>
Rows(i + 1).EntireRow.Insert 'Insert new row<o:p></o:p>
Cells(i + 1, myCol) = Mid(myString, myStart, myEnd - myStart + 1) 'Enter split value<o:p></o:p>
Cells(i + 1, myCol - 1) = Cells(i, myCol - 1) 'Copy value from above for column to left of myCol<o:p></o:p>
myEnd = j - 1<o:p></o:p>
End If<o:p></o:p>
Next j<o:p></o:p>
Cells(i, myCol) = Left(myString, myEnd) 'Enter last value in original cell<o:p></o:p>
End If<o:p></o:p>
Next i<o:p></o:p>
<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
<o:p></o:p>
End Sub


This has worked beautifully for me and am wondering whether i can slightly change the code up above slightly just so its more useful. Applying the code above I now have the situation below:
<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=464><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" span=4 width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=116>How it looked before the code:</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=116> </TD></TR><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 47.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=63>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 1
Price 2
Price 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 45.75pt; mso-height-source: userset" height=61><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=61>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 1
Store 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Very Delicious</TD></TR><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 31.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=42></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>How it looks after the code: </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 1 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Delicious</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Price 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 1 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Fruit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Very Delicious</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Pear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=116>Store 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>
It would be nice to have "Fruit" and "Delicious" carry down from the first two rows as well, and the same thing for "Fruit" and "Very Delicious" for the two "Pear" rows. Any suggestions? But Joe4 - thanks for replying to another post and being very useful already, for me!
 
Upvote 0
Sure, insert these two rows before:

myEnd = j - 1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Rich (BB code): 
Cells(i + 1, myCol + 1) = Cells(i, myCol + 1) 'Copy value from above for column 1 to right of myCol<o:p></o:p>
Cells(i + 1, myCol + 2) = Cells(i, myCol + 2) 'Copy value from above for column 2 to right of myCol<o:p></o:p>

Cheers

Dan
 
Upvote 0
This absolutely works perfectly. If there's ever a time where I have 20 columns of data rather than 4 columns of data and still want to make sure that for the newly inserted rows, cells get copied down through column 20 - is there an efficient way to ensure this happens.

Thanks again for the swift reply!


the most updated code i have is below, just in case anyone else ever wants to take a look. Wasnt sure whether i should remove "Cells(i + 1, myCol - 1) = Cells(i, myCol - 1) 'Copy value from above for column to left of myCol" before "myEnd = j - 1" but left it in and seems to work great.

---
ub MySplit()

Application.ScreenUpdating = False

Dim myLastRow As Long
Dim myCol As Long
Dim myLen As Long
Dim myString As String
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim j As Long

' Copy data from sheet1 to sheet2
Sheets("Sheet1").Select
Cells.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

' Set column with line feeds ("B"=2)
myCol = 2

' Find last row of data in specified column
myLastRow = ActiveSheet.Cells(Rows.Count, myCol).End(xlUp).Row

For i = myLastRow To 1 Step -1 'Loop through all columns
myLen = Len(Cells(i, myCol)) 'Get length of entry
If myLen > 0 Then 'If lenghth>0, capture string and loop through characters
myString = Cells(i, myCol)
myEnd = myLen
For j = myLen To 1 Step -1
If Asc(Mid(myString, j, 1)) = 10 Then 'Look for line breaks
myStart = j + 1
Rows(i + 1).EntireRow.Insert 'Insert new row
Cells(i + 1, myCol) = Mid(myString, myStart, myEnd - myStart + 1) 'Enter split value
Cells(i + 1, myCol - 1) = Cells(i, myCol - 1) 'Copy value from above for column to left of myCol
Cells(i + 1, myCol + 1) = Cells(i, myCol + 1) 'Copy value from above for column 1 to right of myCol
Cells(i + 1, myCol + 2) = Cells(i, myCol + 2) 'Copy value from above for column 2 to right of myCol
myEnd = j - 1
End If
Next j
Cells(i, myCol) = Left(myString, myEnd) 'Enter last value in original cell
End If
Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
If you had more columns I would substitute the code with this:

Code: 
Sub Package()
Dim CalculatedValue
Range("L2").Select
Do
    If ActiveCell.Value = "" Then
        CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5
        ActiveCell.Value = IIf(CalculatedValue = 31.5 Or CalculatedValue = 33 Or CalculatedValue = 61.5 Or CalculatedValue = 63, 34.5, CalculatedValue)
    End If
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
 
 
Sub MySplit()
Application.ScreenUpdating = False
Dim myLastRow As Long
Dim myCol As Long
Dim myLen As Long
Dim myString As String
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim j As Long
Dim X As Integer
' Copy data from sheet1 to sheet2
Sheets("Sheet1").Select
Cells.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Set column with line feeds ("B"=2)
myCol = 2
' Find last row of data in specified column
myLastRow = ActiveSheet.Cells(Rows.Count, myCol).End(xlUp).Row
For i = myLastRow To 1 Step -1 'Loop through all columns
myLen = Len(Cells(i, myCol)) 'Get length of entry
If myLen > 0 Then 'If lenghth>0, capture string and loop through characters
myString = Cells(i, myCol)
myEnd = myLen
For j = myLen To 1 Step -1
If Asc(Mid(myString, j, 1)) = 10 Then 'Look for line breaks
myStart = j + 1
Rows(i + 1).EntireRow.Insert 'Insert new row
Cells(i + 1, myCol) = Mid(myString, myStart, myEnd - myStart + 1) 'Enter split value
Cells(i + 1, myCol - 1) = Cells(i, myCol - 1) 'Copy value from above for column to left of myCol
For X = 1 To 20 'Change this to the number of columns to the right of the master column
    Cells(i + 1, myCol + X) = Cells(i, myCol + X) 'Copy value from above for columns to right of myCol
Next
myEnd = j - 1
End If
Next j
Cells(i, myCol) = Left(myString, myEnd) 'Enter last value in original cell
End If
Next i
Application.ScreenUpdating = True
End Sub

Update this:
For X = 1 To 20 'Change this to the number of columns to the right of the master

Change the 20 to however many columns you want to include to the right of your column with the carraige returns in.
 
Upvote 0
In fact, use this and provided you have headings on row 1 it will calculate how many columns it should do it on :).

Code: 
Sub Package()
Dim CalculatedValue
Range("L2").Select
Do
    If ActiveCell.Value = "" Then
        CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5
        ActiveCell.Value = IIf(CalculatedValue = 31.5 Or CalculatedValue = 33 Or CalculatedValue = 61.5 Or CalculatedValue = 63, 34.5, CalculatedValue)
    End If
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
 
 
Sub MySplit()
Application.ScreenUpdating = False
Dim myLastRow As Long
Dim myCol As Long
Dim myLen As Long
Dim myString As String
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim j As Long
Dim X As Integer
' Copy data from sheet1 to sheet2
Sheets("Sheet1").Select
Cells.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Set column with line feeds ("B"=2)
myCol = 2
' Find last row of data in specified column
myLastRow = ActiveSheet.Cells(Rows.Count, myCol).End(xlUp).Row
For i = myLastRow To 1 Step -1 'Loop through all columns
myLen = Len(Cells(i, myCol)) 'Get length of entry
If myLen > 0 Then 'If lenghth>0, capture string and loop through characters
myString = Cells(i, myCol)
myEnd = myLen
For j = myLen To 1 Step -1
If Asc(Mid(myString, j, 1)) = 10 Then 'Look for line breaks
myStart = j + 1
Rows(i + 1).EntireRow.Insert 'Insert new row
Cells(i + 1, myCol) = Mid(myString, myStart, myEnd - myStart + 1) 'Enter split value
Cells(i + 1, myCol - 1) = Cells(i, myCol - 1) 'Copy value from above for column to left of myCol
For X = 1 To Cells(1, Columns.Count).End(xlToLeft).Column - 2
    Cells(i + 1, myCol + X) = Cells(i, myCol + X) 'Copy value from above for columns to right of myCol
Next
myEnd = j - 1
End If
Next j
Cells(i, myCol) = Left(myString, myEnd) 'Enter last value in original cell
End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Nice. And apprently the program is robust enough to know that a blank cell (say column P is blank) but that it should continue to column Z - the true end in a case I ran.

So I'm running both macros now, first the shorter macro and then the much longer macro since each appear to be independant codes. I tested both codes on a larger data set with 300 rows and 25 columns and for some reason on column L row 185 is where the program stops - I noticed that this particular cell was blank. Do blank cells across the entire range affect the program? It doesnt seem like it, but making sure.
 
Upvote 0
I would assume That this:

Loop Until IsEmpty(ActiveCell.Offset(0, -2))

Is empty. The offset is -2 on the column meaning Column J.
 
Upvote 0
You're right J185 is empty (and in this case, it so happens that L185 was empty too). Seems like empty rows don't affect the overall output.

I've been running the second macro without running the first and I don't see a difference. Is the first macro (below) needed, if so, why?

Dim CalculatedValue
Range("L2").Select
Do
If ActiveCell.Value = "" Then
CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5
ActiveCell.Value = IIf(CalculatedValue = 31.5 Or CalculatedValue = 33 Or CalculatedValue = 61.5 Or CalculatedValue = 63, 34.5, CalculatedValue)
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
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
901
epactheactor
E
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
508
esben
E

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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