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
I didn't write it to work with any headers. I thought you were trying to replace one line with ten.

I need a little more clarification about what you want.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi there

There are column headers for all 20 columns - I used col 17, col 18 as an example. I should have explained - my apologies.

So - if a row needs to be split 10 times for example - I will end up with 10 rows of data - the only column that will change is column 18 which is what happened from your code and got updated with the data from col 20 and col 20 did clear as it should as per your code. Other than the column headings being there - the samples I showed is the required result.

Does that help?

Thanks!
 
Upvote 0
Code: 
Sub tester()
 
Dim a As Variant, b, c, d 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)
 
    If c = 0 Then
        d = Cells(Rows.Count, 18).End(xlUp).Row
        Cells(d, 20).ClearContents
 
    Else
        d = Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
 
    End If
 
    Cells(d, 18) = a(c)
    Cells(d, 17) = Cells(b, 17).Offset(-1, 0)
    Cells(d, 19) = Cells(b, 19).Offset(-1, 0)
 
Next
 
End Sub

Let me know.
 
Upvote 0
SOOO CLOSE

My results were:
<TABLE style="WIDTH: 675pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=899><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 178pt; mso-width-source: userset; mso-width-alt: 8667" width=237><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=21 width=64>col 13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>col 14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>col 15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>col 16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=103>col17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 130pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=173>col 18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=130>col 19</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 178pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=237>col 20</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=22>AREA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>LICENCE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>SECTION</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>JOB</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 130pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl70 width=173>2011000000237</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl71 width=130>Multi-part</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 178pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=237> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000238</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000239</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000240</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000241</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000242</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000243</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000244</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>0020110000245</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000246</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>T659145338</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>2011000000247</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD></TR></TBODY></TABLE>


The data in the columns before (so from 1-16) were empty and they need to be populated below just like col. 17. Also I lost the data in the first row - so #2011000000236 is gone from col 18- looks like 2011000000237 replaced it (from col 20).
This is looking sooo much closer to what will work.....You are a genius.
 
Upvote 0
Don't make me laugh.

Let me know if it's through.

Code: 
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(d, 20).ClearContents
    For e = 13 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
 
Upvote 0
Hi again

I changed one thing - For e= 1 To 19 and it worked for what I needed and copied all the columns as it should - so thanks for that and helping me.


The only thing that didn't work now is col 20 contents did not clear, but I see it in your code, so that is strange? Does that need to be in a different location in the code for it to work??? Just a guess.

Lynn
 
Upvote 0
Lynn

Give this a try in another copy of your workbook.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> SplitData2()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><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> r = LR <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>        s = Cells(r, 20).Value<br>        rws = Len(s) - Len(Replace(s, "~", "")) + 1<br>        <SPAN style="color:#00007F">With</SPAN> Cells(r, 1).Resize(, 19)<br>            .Copy<br>            .Offset(1).Resize(rws).Insert Shift:=xlDown<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Columns(20).ClearContents<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</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>
 
Last edited:
Upvote 0
Hi there

Well I tried your code as well Peter_SSs and got very different results. Col 20 disappeared along with the data and everything else duplicated itself. I got two header rows as well - which I don't need. So that is not as close as the previous codes. See below

<TABLE style="WIDTH: 267pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=356><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=21 width=103>col 17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=99>col 18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=64>col 19</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=90></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=21>col 17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>col 18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>col 19</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=90> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 height=21 width=103>T659145338</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68 width=99>2011000000236</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl69 width=64>Multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR></TBODY></TABLE>
 
Upvote 0
It's targeting the wrong cell.

Replace this line: "Cells(d, 20).ClearContents"

With this: "Cells(Cells(Rows.Count,20).End(xlUp).Row,20)"

Peter's solution is the better of the two options, though, for both of the reasons I mentioned back in post #7. When the kinks are smoothed out you should go with his work.
 
Upvote 0
Lynnmc26 said:
Col 20 disappeared
Click to expand...
Column 20 is still there, I have just deleted the data from it. Isn't that what you wanted by stating the following?
Lynnmc26 said:
so it is not only split, but an extra line added and col 20 contents cleared.
Click to expand...




Lynnmc26 said:
... everything else duplicated itself.
Click to expand...
That is what is required isn't it except in column 18 where I missed the fact that the number should be incremented? Corrected code below.


Lynnmc26 said:
I got two header rows as well - which I don't need.
Click to expand...
We just need to stop the loop at row 2 instead of row 1.


Try this version.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> SplitData2()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><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> r = LR <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        s = Cells(r, 20).Value<br>        rws = Len(s) - Len(Replace(s, "~", "")) + 1<br>        <SPAN style="color:#00007F">With</SPAN> Cells(r, 1).Resize(, 19)<br>            .Copy<br>            .Offset(1).Resize(rws).Insert Shift:=xlDown<br>            .Offset(, 17).Resize(rws + 1, 1).DataSeries _<br>                Type:=xlLinear, Step:=1, Trend:=<SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Columns(20).ClearContents<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</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
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