Split cell data in mutiple rows
 

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

Split cell data in mutiple rows
 

Hi, I had a bit of a play round, unfortunately time forbids.. However the
following macro works except for a slight bug. Try it using the Macro "Step
Into" and you will see the gist of it then you can probably fix the error and
do a cleanup in the work column I use.

Good luck.

Sub Test()
'
'

'
Range("C2").Select
ActiveCell.Offset(0, 2) = "=Find("","",C2)"
On Error GoTo NoComma
Do Until ActiveCell = ""
If ActiveCell.Offset(0, 2) 0 Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Copy
ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 2).Select
x = ActiveCell.Offset(0, 2)
y = Len(ActiveCell)
z = y - x
x = x - 1
ActiveCell = Left$(ActiveCell, x)
ActiveCell.Offset(1, 0).Select
ActiveCell = Right$(ActiveCell, z)
ActiveCell.Offset(-1, 0).Select
End If
NoComma:
ActiveCell.Offset(1, 2).Select
ActiveCell.Offset(-1, 0).Copy
ActiveSheet.Paste
ActiveCell.Offset(0, -2).Select
Loop
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

Split cell data in mutiple rows
 

TRY ...

Sub CostC()

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Dim lasrow As Long, r As Long, rr As Long

rr = 1
ws1.Cells(1, 1).Resize(1, 4).Copy ws2.Cells(rr, 1)
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
ns = 1
Do
rr = rr + 1
ws1.Cells(r, 1).Resize(1, 4).Copy ws2.Cells(rr, 1)
n = InStr(ns, .Cells(r, 3), ",")
If n < 0 Then
ws2.Cells(rr, 3) = Mid(.Cells(r, 3), ns, n - ns)
ns = n + 1
Else
ws2.Cells(rr, 3) = Mid(.Cells(r, 3), ns, 255)
End If
Loop Until n = 0
Next r
End With
End Sub

HTH

" 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