Solved: Recognize mutiples and add rows

[jrbuno]

I am sub-beginner in VBA-my boss wants me to learn a little basic programming so I thought this would be a good place to start. I am not sure if I can complete this task with a macro or what...Excell 2007I get an automatic excel report that lists a code or codes an individual has used; I want to know how to tell the program to recognize if there is multiple codes in a cell (always seperated by a comma) and if there is multiple codes, add an individual row for every individual code. Hopefully my attachment helps; 2 tabs.I hope I am clear enough; I think if I can get this, I will be able to build so much off of it.Thanks.

[mancubus]

hi and wellcome to VBAX.

try below code with a copy of your workbook.

Sub StringSplit()
Dim varString As Variant
Dim i As Integer, lrow As Long, rowIns As Long

lrow = 2
Do
varString = Split(Replace(Cells(lrow, "C").Value, " ", ""), ",")
If LBound(varString) = UBound(varString) Then
Else
rowIns = UBound(varString) - LBound(varString)
Cells(lrow, "C").Offset(1).Resize(rowIns).EntireRow.Insert
Cells(lrow, "C").Resize(rowIns + 1, 1) = Application.Transpose(varString)
End If
lrow = lrow + 1
Loop Until Cells(lrow, "C") = ""
End Sub

[Bob Phillips]

Public Sub ProcessData()
Dim vecCodes As Variant
Dim Lastrow As Long
Dim Numrows As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If InStr(.Cells(i, "C").Value, ",") > 0 Then

vecCodes = Split(.Cells(i, "C").Value, ",")
Numrows = UBound(vecCodes) - LBound(vecCodes) + 1
.Rows(i + 1).Resize(Numrows - 1).Insert
.Cells(i, "C").Resize(Numrows) = Application.Transpose(vecCodes)
End If
Next i
End With

Application.ScreenUpdating = True

End Sub

[jrbuno]

Worked like a charm! Thanks for being so helpful and welcoming.

[mancubus]

you are wellcome jrbuno.

pls mark the thread as solved from thread tools dropdown...