Hi,
Thanks for your help,
I tried using the code but its only working for the last row which have
multiple values and not for the first or the second row.
And i also wanted to know how can i extend this to more than 3 columns
which i have shown you till now coz i have to work on a file which have
atleast till Z columns and h ave to split on only columns


Please help

Thanks

JMB wrote:
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