Hi Rick,

As long as there are no blanks between the first and last rows.., your
suggestion is nice and simple.

I considered the existence of blank cells/rows a possibility so wrote code
accordingly. It seems to work without error when tested with various contents
in the source range, nicely skipping over any empty cells.

Best regards,
Garry

"Rick Rothstein (MVP - VB)" wrote:

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?

If Garry (GS) is right and you simply ran into empty the rows after your
data, you could modify your code like this...

Sub Macro2()
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
If Ubound(var) < 0 Then Exit For ' or perhaps Exit Sub
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

Rick