This seemed to work okay. Where FirstRow is the first row of data (excludes
header), DataCol is the Column whose data is being split (in this example,
I'm using column C, which is 3). Make sure your data is backed up and be
aware that this newsgroup often wraps code in funny places, so look for any
lines in red after you paste into a module and put back together (I tried to
wrap the longer lines using the line continuation character " _", but may not
help much.


Sub Test()
Const FirstRow As Long = 2
Const DataCol As Long = 3
Dim LastRow As Long
Dim i As Long
Dim varTemp As Variant

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For i = LastRow To FirstRow Step -1
If InStr(1, .Cells(i, DataCol).Value, _
",", vbTextCompare) 0 Then
varTemp = Split(.Cells(i, DataCol).Value, _
",", -1, vbTextCompare)
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol)(UBound(varTemp), _
1)).Offset(1, 0).EntireRow.Insert
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol).Offset(UBound(varTemp), _
0)).Value = Application.Transpose(varTemp)
.Range(.Cells(i, .Cells(i, DataCol).CurrentRegion.Columns(1).Column), _
.Cells(i + UBound(varTemp), DataCol - 1)).FillDown
.Range(.Cells(i, DataCol + 1), _
.Cells(i + UBound(varTemp), DataCol + 1)).FillDown
End If
Next i
End With

End Sub

" wrote:

Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks