Macro to split the contents in a single cell separated by "," intonext cell in that column

[anshu minocha]

Hi,

I have sheet 1:

column A column B columnC
WR# Phase SP#
Row2 60625 1-0110 60625RB1,60625NS1,60625GW1,60625BB1

Expected Output on clicking a button:

column A column B columnC
WR# Phase SP#
Row2 60625 1-0110 60625RB1
Row3 60625NS1
Row4 60625GW1
Row5 60625BB1


Is there a way to split the contents in cell C2 separated by comma (,)
and place it on next cell in column C itself?
Please suggest.Thankyou.

 

[anshu minocha]

Hi,

I have sheet 1:

        column A    column B  columnC
        WR#          Phase       SP#
Row2 60625       1-0110       60625RB1,60625NS1,60625GW1,60625BB1

Expected Output on clicking a button:

         column A    column B  columnC
         WR#          Phase       SP#
Row2 60625       1-0110       60625RB1
Row3                                  60625NS1
Row4                                 60625GW1
Row5                                 60625BB1

Is there a way to split the contents in cell C2 separated by comma (,)
and place it on next cell in column C itself?
Please suggest.Thankyou.

 

[anshu minocha]

Found the code:
http://groups.google.com/group/micr...t&q=Split+contents+of+a+cell#a15587d778c2c275
thanks

 

[Rick Rothstein]

Here is another way to do it...

Sub SplitDownwardColumnC()
Dim X As Long, LastRow As Long, StartRow As Long
Dim Cell As Range, Parts As Variant
StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To StartRow Step -1
Parts = Split(Cells(X, "C").Value, ",")
Rows(X).Offset(1).Resize(UBound(Parts)).Insert
Cells(X, "C").Resize(UBound(Parts) + 1).Value = _
WorksheetFunction.Transpose(Parts)
Next
End Sub