Further Code Writing - Splitting Rows, copying data

L

Lynnmc26

New Member
Joined
Apr 4, 2011
Messages
24
Hi all
I currently have a code that was provided here on this Forum and it looks like this:

Option Explicit
Sub SplitData()
' hiker95, 04/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=541113
Dim LR As Long, a As Long, Sp
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 20).End(xlUp).Row
For a = LR To 1 Step -1
If InStr(Cells(a, 20), "~") > 0 Then
Sp = Split(Cells(a, 20), "~")
Rows(a + 1).Resize(UBound(Sp)).Insert
Rows(a).Resize(UBound(Sp) + 1).Value = Rows(a).Value
Cells(a, 20).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
End If
Next a
Application.ScreenUpdating = True
End Sub

The code above (20 above being the 20th column) splits the sample below

col 17 col 18 col19 col20
<TABLE style="WIDTH: 663pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=884 border=0><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8155" width=223><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 271pt; mso-width-source: userset; mso-width-alt: 13202" width=361><TBODY><TR style="HEIGHT: 52.5pt; mso-height-source: userset" height=70><TD class=xl63 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 52.5pt; BACKGROUND-COLOR: transparent" width=93 height=70>T659145338</TD><TD class=xl63 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 95pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=127>2011000000236</TD><TD class=xl63 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 167pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=223> </TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 60pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 271pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=361>2011000000237~2011000000238~2011000000239~2011000000240~2011000000241~2011000000242~2011000000243~2011000000244~2011000000245~2011000000246~2011000000247</TD></TR></TBODY></TABLE>

to this:

<TABLE style="WIDTH: 252pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=335 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000237</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000238</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000239</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000240</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000241</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000242</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000243</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000244</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000245</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000246</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl64 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD class=xl65 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 83pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>2011000000247</TD></TR></TBODY></TABLE>

what we need (change from client) as a result now is:

<TABLE style="WIDTH: 252pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=335 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>col 17</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=90>col 18</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>col 19</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=111>col 20</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000236</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000237</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000238</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000239</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000240</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000241</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000242</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000243</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000244</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000245</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000246</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=70 height=16>T659145338</TD><TD class=xl67 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 68pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>2011000000247</TD><TD class=xl66 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 48pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Multi-part</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

so it is not only split, but an extra line added and col 20 contents cleared.

I am not even sure if this is possible with code but would like someones opinion.

Thank you very very much!:confused:

Lynn
 
Sort by date Sort by votes
Peter - I sort of got the impression the number shouldn't just be incrememented, but rather preserved in its original form. Hence split as it was used in the original code from hiker.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Glory said:
Peter - I sort of got the impression the number shouldn't just be incrememented, but rather preserved in its original form. Hence split as it was used in the original code from hiker.
Click to expand...
You are probably right. I think I mis-understood the requirement. I'll have another think about it.
 
Upvote 0
Hi
I did the replace and now looks like this - but shows up in red and gives me an error message
Sub tester()
Dim a As Variant, b, c, d, e As Long
a = Split(Cells(Rows.Count, 20).End(xlUp).Value, "~")
b = Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
For c = 0 To UBound(a)
d = Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
Cells(Cells(Rows.Count,20).End(xlUp).Row,20)
For e = 1 To 19
Select Case e <> 18
Case True
Cells(d, e) = Cells(b, e).Offset(-1, 0)
Case False
Cells(d, e) = a(c)
End Select
Next
Next
End Sub
___________________
Error Message

Compile Error
Syntax Error
 
Upvote 0
Peter_SSs said:
I'll have another think about it.
Click to expand...
Try hiker's code with these few modifications. I've marked the changed lines of code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SplitData()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Sp<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Cells(Rows.Count, 20).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> a = LR <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1                                     <SPAN style="color:#007F00">'*****</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> InStr(Cells(a, 20), "~") > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Sp = Split(Cells(a, 20), "~")<br>            Rows(a + 1).Resize(UBound(Sp) + 1).Insert           <SPAN style="color:#007F00">'*****</SPAN><br>            Rows(a).Resize(UBound(Sp) + 2).Value _<br>                = Rows(a).Value                                 <SPAN style="color:#007F00">'*****</SPAN><br>            Cells(a + 1, 18).Resize(UBound(Sp) + 1).Value _<br>                = Application.Transpose(Sp)                     <SPAN style="color:#007F00">'*****</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> a<br>    Columns(20).ClearContents                                   <SPAN style="color:#007F00">'*****</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
ALMOST!!!!

All the data split and copied down - the ONLY thing is - lost the col 20 heading and I still need that column heading there at this point.

Is that possible?

THANK YOU!!!!

Lynn
 
Upvote 0
Sure, replace the 'ClearContents' line with this one
Code: 
Range("T2", Range("T" & Rows.Count).End(xlUp)).ClearContents
 
Upvote 0
Worked Perfectly.

Everyone here on this site has been amazing for me. Thank you so much for your help.

Have a wonderful day!:)
 
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
624
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,621
Messages
6,179,938
Members
452,949
Latest member
beartooth91

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