Duplicate rows based on its data

Question

I have an excel file which I'll use to import hundreds of products in an e-shop. The excel have columns like this

Title | SKU | Size | Price | etc

I asked my clients to make the excel in very specific format (especially on rows). I wanted each variation of the product (size) to has its own row. Lets say for a shirt which has 3 sizes (S, M, L), it should have 3 rows for each variation. Well, they sent me the excel with the variations to be in one row, like this

S/M/L

I'm totally noob on Excel. How can I make a macro lets say, to check for the variations which are seperated by the slash and make x number of duplicated rows? And also if (that would make me veeeery happy) can keep only one variation in each row.

Thank you :)

Answer 1

Right click on sheet's name and pick "Show code".

Paste the following code:

Sub Expandrows()
Dim sizes() As String
For Each c In Selection
    With c
        If .Value <> "" Then
            sizes = Split(.Value, "/")
            If UBound(sizes) > 0 Then
                For i = UBound(sizes) To LBound(sizes) Step -1
                    Rows(.Row).Copy
                    Rows(.Row + 1).Insert shift:=xlDown
                    .Offset(1) = sizes(i)
                Next i
                Application.CutCopyMode = False
                Rows(.Row).Delete
            End If
        Else: Exit For
        End If
    End With
Next c
End Sub

Go back to your sheet, select the "Size" column by clicking on its header.

Under the "Developer" menu, run macro from "Macros".