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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
I'm super confused.

What are you trying to do? Add an extra column where and when?

Columns(20).ClearContents

Edit: Save your workbook and work in a separate copy so you don't screw something up you can't recover.

Do you want only Range("T" & x) to clear, where x is the row you're editing at the given time?

You need a workbook change event for that.
 
Upvote 0
Hi there
Sorry - tried to not be confusing here.

The original code would split the row and end up with 11 rows

I would actually need 12 rows instead of 11 - that is what I mean by adding another row.

All the data in col. 20 would have to generate into col 18 instead of repeating itself and then col 20 - the contents cleared.

Does that help?
 
Upvote 0
Actually, I don't think I should answer this question. I can see a way to do it, but it's not the way hiker did it. The code you posted combines several steps into single lines, and I would need several lines to do each step. I'm not comforable messing with it.
 
Upvote 0
I would be happy to see your way - if it works - that is really what I need to accomplish....a result.

Please and thanks!
 
Upvote 0
Okay, gimme another couple minutes. I need to ask about skipping iterations in for loops.

Edit: It's going to look something like this.

Code: 
'Not working yet
 
Sub tester()
a = Split(Cells(Rows.Count, 20).End(xlUp).Value, "~")
For b = 1 To UBound(a)
    c = Cells(Rows.Count, 20).End(xlUp).Offset(b, 0).Row
    For d = 17 To 19 'In progress
        Cells(c, d) = Cells(c - 1, d)
 
    Next
    Cells(c, 20) = a(b)
Next
End Sub

Here are some potential drawbacks.

It looks like part of the point of hiker's code might have been to make sure that nothing was overwritten below the code that's being used. Also, latency will be increased using this method, but probably not noticeably so.
 
Last edited:
Upvote 0
Seems to work.

Code: 
Sub tester()
 
Dim a As Variant, b, c, d As Integer
 
a = Split(Cells(Rows.Count, 20).End(xlUp).Value, "~")
 
For b = 0 To UBound(a)
 
    If b = 0 Then

        c = Cells(Rows.Count, 18).End(xlUp).Row
        Cells(c, 20).ClearContents
 
    Else
        c = Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
    
    End If
 
    Cells(c, 18) = a(b)
    Cells(c, 17) = Cells(c, 17).Offset(-1, 0)
    Cells(c, 19) = Cells(c, 19).Offset(-1, 0)
 
Next
End Sub
 
Upvote 0
I am going to give it a try - and yes will be a sample worksheet first before on the original.

Thanks - will let you know.

Lynn
 
Upvote 0
Hi there - gave it a try - not quite.

My result was:
<TABLE style="WIDTH: 658pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=877><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 384pt; mso-width-source: userset; mso-width-alt: 18724" width=512><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 125pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=21 width=167>COL 17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 101pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=134>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: 384pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=512>COL 20</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 125pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl63 height=21 width=167>COL 17

</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 101pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=134>2011000000237</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=64>COL 19</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #d0d7e5 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 384pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=512> </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=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>20110000 00245</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: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></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=xl66 height=20>COL 17</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=xl66>COL 19</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>COL 17</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=xl66>COL 19</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR></TBODY></TABLE>

so it replaced the data in the field with the column header.

Can you have a look?
 
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,617
Messages
6,179,915
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