I have a spreadsheet with rows of data. Each row represents a separate product and column G contains all the colours for the product in each row. Each of the colours is separated by the pipe symbol. I need a macro to look at column G in each row and then create copies of each row depending on how many colours there are for the product.
I have attached an excel file with sample data. Sheet 1 contains 2 rows (excluding the header row). The first row contains 4 colours in Column G and the second row contains 3 colours in Column G. If you look at Sheet 2, you can see that there are now 7 rows, ie. 4 for the first SKU (in sheet 1) and 3 for the second SKU (in sheet 1) and each of the colours from column G is now separated out onto its own row.
I have the following VBA script that works if the colours are in column B but because mine are in column G it doesn't seem to work:
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set c = Cells(Idx, 2)
If InStr(1, c, "|") > 0 Then
Colors = Split(c, "|")
NrColors = UBound(Colors)
Rows(c.Row + 1 & ":" & c.Row + NrColors).Insert Shift:=xlDown
For Idx1 = 0 To NrColors
c.Offset(Idx1) = Colors(Idx1)
I wondered if someone could help me revise the above script so that it works with my spreadsheet configuration, ie. where the colours are in column G?
Would really appreciate any help.
Thanks in advance.