delimited data in column

  • Thread starter Thread starter gareth
  • Start date Start date
G

gareth

Column F has data in the following format:

Column F
1.01
0.94
1.44,0.22
3.03
1.87,0.05
3.99
2.74,0.15,1.68,0.09
0.76
3.39
20.14

Where there are 2 or more numbers in each cell I need to
insert a row(s). I then need to put all the numbers into
the column and then total the cell into column E. The
finished data would look like this:

E F
1.01 1.01
0.94 0.94
1.66 1.44
0.22
3.03 3.03
1.92 1.87
0.05
3.99 3.99
4.66 2.74
0.15
1.68
0.09
0.76 0.76
3.39 3.39
20.14 20.14

Thanks in advance.

Gareth
 
Gareth

Try this

Sub DelimitedCols()

Dim DelList As Variant
Dim i As Long
Dim CellRw As Long
Dim sh As Worksheet
Dim Ttl As Double

Set sh = ThisWorkbook.Sheets(1)

For CellRw = 12 To 3 Step -1
DelList = Split(sh.Range("F" & CellRw).Value, ",")
Ttl = DelList(LBound(DelList))
If UBound(DelList) > LBound(DelList) Then
For i = LBound(DelList) + 1 To UBound(DelList)
sh.Range("F" & CellRw).Offset(1, 0).EntireRow.Insert
sh.Range("F" & CellRw).Offset(1, -1).Value = DelList(i)
Ttl = Ttl + DelList(i)
Next i
sh.Range("F" & CellRw).Value = DelList(LBound(DelList))
sh.Range("F" & CellRw).Offset(0, -1).Value = Ttl
Else
sh.Range("F" & CellRw).Offset(0, -1).Value = _
sh.Range("F" & CellRw).Value
End If
Next CellRw

End Sub
 
Back
Top