Hi Team,
I have been following the thread below to split comma separated text cell in to multiple rows. I am very close to making it work but for some reason it is not copying down adjacent column information.
http://www.mrexcel.com/forum/excel-...into-multiple-rows-using-comma-delimiter.html
For example, I have data that runs from A-G columns. The data that needs to be split into multiple rows is in column A and the code copies down associated information for column B, C and one date column in F but does not take down the information for columns D, E, and G. Instead it gives an entry of "=R[-1]C".
<tbody>
</tbody>
This makes me think that the code is not working in the highlighted red area:
[
Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
For i = LR To 1 Step -1
With Range("B" & i)
If InStr(.Value, ",") = 0 Then
.Offset(, -1).Value = .Value
Else
X = Split(.Value, ",")
.Offset(1).Resize(UBound(X)).EntireRow.Insert
.Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End If
End With
Next i
Columns("B").Delete
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B:G" & LR)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
]
Please let me know how I can tweak the code to work for all columns. I would really appreciate your help.
Thank you!
I have been following the thread below to split comma separated text cell in to multiple rows. I am very close to making it work but for some reason it is not copying down adjacent column information.
http://www.mrexcel.com/forum/excel-...into-multiple-rows-using-comma-delimiter.html
For example, I have data that runs from A-G columns. The data that needs to be split into multiple rows is in column A and the code copies down associated information for column B, C and one date column in F but does not take down the information for columns D, E, and G. Instead it gives an entry of "=R[-1]C".
A | B | C | D | E | F | G |
WireslessTowers141009 | 00TE000001BzTkf | Jessica | Dan Anderson | Purple | 10/8/2014 | Yellow |
HAWK141008 | 00TE000001BzTkf | Jessica | =R[-1]C | =R[-1]C | 10/8/2014 | =R[-1]C |
FLTComdata141007 | 00TE000001BzTkf | Jessica | =R[-1]C | =R[-1]C | 10/8/2014 | =R[-1]C |
<tbody>
</tbody>
This makes me think that the code is not working in the highlighted red area:
[
Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
For i = LR To 1 Step -1
With Range("B" & i)
If InStr(.Value, ",") = 0 Then
.Offset(, -1).Value = .Value
Else
X = Split(.Value, ",")
.Offset(1).Resize(UBound(X)).EntireRow.Insert
.Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End If
End With
Next i
Columns("B").Delete
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B:G" & LR)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
]
Please let me know how I can tweak the code to work for all columns. I would really appreciate your help.
Thank you!