Convert data to rows 1

[Brianfree]

Hi, can anyone assist me on a macro to convert my data so if a certain field has a "/" in it that it is split and moved to a new row?

Please see example below...

ref1	ref2	ref3	ref4	         ref5	           ref6	ref7
a	abc123	17	product 1	12345/134344	  1	55
b	abc466	60	product 2	17776	           2	66
c	abc4322	24	product 3	12222/35543/445634  6	3
d	abb43	9	product 4	12234	           6	345

Would like this converting to..

ref1	ref2	ref3	ref4	         ref5	ref6	ref7
a	abc123	17	product 1	12345	1	55
a	abc123	17	product 1	134344	1	55
b	abc466	60	product 2	17776	2	66
c	abc4322	24	product 3	12222	6	3
c	abc4322	24	product 3	35543	6	3
c	abc4322	24	product 3	445634	6	3
d	abb43	9	product 4	12234	6	345

Please can anyone help!

Many thanks

Brian

 

[byundt]

Here is a macro to split the 5th column on the slash:

Sub SlashSplit()
Dim rg As Range
Dim sDelimiter As String
Dim v As Variant
Dim col As Long, i As Long, k As Long, n As Long
Application.ScreenUpdating = False
Set rg = Range("A1").CurrentRegion
n = rg.Rows.Count
col = 5 'Column number to be split
sDelimiter = "/"
For i = n To 1 Step -1
    Set v = Nothing
    v = Split(rg.Cells(i, col), sDelimiter)
    k = UBound(v)
    If k > 0 Then
        rg.Cells(i + 1, col).Resize(k, 1).EntireRow.Insert
        rg.Rows(i + 1).Resize(k, rg.Columns.Count).Value = rg.Rows(i).Value
        rg.Cells(i, col).Resize(k + 1, 1).Value = Application.Transpose(v)
    End If
Next
End Sub

Brad