Copy rows on criteria VBA

Question

I need some ideas how to copy lines which matches criteria.

I have such data:

   A        B                          C     D
1   Country  Brand                      Model Year
2   France   Peugeot, Citroen (France)  Car   2003
3   Germany  VW, Opel, BMW (Germany)    Car   2003
...

The goal is that each car brand with it's parameters would be in separate lines/ What I'd like to do is to copy line #2 to the bottom of the table and copy line #3 2 times to the bottom of the table. Also create column F which shows the brand.

Result should look like this:

    A        B                          C     D      F
1   Country  Brand                      Model Year
2   France   Peugeot, Citroen (France)  Car   2003   Peugeot
3   France   Peugeot, Citroen (France)  Car   2003   Citroen
4   Germany  VW, Opel, BMW (Germany)    Car   2003   VW
...

Also, I have Brand list in separate sheet.

Should I try search for a , or try to match those brands from list in separate sheet?

I am new with vba so any ideas, suggestions will be appreciated!

See [How to “flatten” or “collapse” a 2D Excel table into 1D](http://stackoverflow.com/questions/687470). — , Apr 21 '16 at 15:13
It is not "unpivot" task. I only wan to copy those lines which have more than one brand. I need to copy as many times as the number of brands in `B` column — AK47, Apr 21 '16 at 15:19
Look into the Split() function in vba. Then use the Ubound of that array to determine whether to copy or not. You will need to also use the Left(Instr()) methods to remove the part in the `()`. All this in a loop that loops through the rows. with a sub loop for each of the entities returned by the split. I would suggest loading everything in arrays and then out put the final array when done. — Scott Craner, Apr 21 '16 at 15:24

C. White · Accepted Answer · 2016-05-12T21:44:03.170

If column B always includes the country from column A in parentheses, consider removing it from the column before performing a split. Something like:

Dim originalWs as Excel.Worksheet, newWs as Excel.Worksheet
Dim countryName as String, allBrands as String, brandArray() as String
Dim rowNumber as long, nextRow as long, i as long

rowNumber = 2 : nextRow = 2

...

countryName = originalWs.Cells(rowNumber,1)
allBrands = originalWs.Cells(rowNumber,2)
brandArray = Split(Replace(allBrands,"(" & countryName & ")",""),",")

...

For i = lbound(brandArray) to ubound(brandArray)
  nextRow = nextRow+1
  With newWs
    .Cells(nextRow,1) = countryName
    .Cells(nextRow,2) = allBrands
    .Cells(nextRow,3) = originalWs.Cells(rowNumber,3)
    .Cells(nextRow,4) = originalWs.Cells(rowNumber,4)
    .Cells(nextRow,5) = brandArray(i)
  End With
Next i

...

The key is that you have to maintain separate counters for:

The row in the original worksheet
The row being inserted into the new worksheet
The brand in the array

Good luck!

Copy rows on criteria VBA

1 Answers1