Hi Garry ! Hello Rick!
There are no blank rows in the data except at the end.

Secondly, I tried both the things.

Garry, when I tried your code, it is extracting the first three
folders in column g. I only want the third folder. Similarly for
column H.

I still got the error, subscript out of range and I found out what is
causing it.

Rick, I got the error with this code too.

There is one directory which does not have a sub directory and that is
where the macro stops.

Can you please assist in rectifying this?

On Jun 13, 5:16 pm, GS wrote:
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- Hide quoted text -

- Show quoted text -