Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

excel vba assistance - row to list of item

Hello All:

I am looking for a macro to split a row of data

Data:

sample|A,B,C
sample2|X,Y,Z

Output

Sample
A
B
C

Sample2
X
Y
Z

thanks
0
CalmSoul
Asked:
CalmSoul
1 Solution
 
Pratima PharandeCommented:
try this from link
http://stackoverflow.com/questions/8560718/split-comma-separated-entries-to-new-rows

I have tried this and works exatly as per you requirment

please finmd attached excel for demo
New-Microsoft-Excel-Worksheet--2.xlsx
0
 
SreeramCommented:
Try this :

Sub Macro1()
    Dim SplitValue As Variant
        SplitValue = Split(ActiveCell.Value, ",")
            ActiveSheet.Range("B1").Select
          For I = 0 To UBound(SplitValue)
            ActiveCell.Value = SplitValue(I)
            ActiveCell.Offset(1, 0).Select
          Next I
End Sub
0
 
SteveCommented:
The following should do what you require quite quickly.
(This assumes that your data has the "|" seperator for titles and commas for the values as per your question)

Sub mySplit()
Dim r As Range
Dim d, a

Set r = Range("A1:A3")

d = Join(Application.Transpose(r), ",,")
d = Replace(d, "|", ",")
a = split(d, ",")
Range("B1").Resize(UBound(a) + 1) = Application.Transpose(a)

End Sub

Open in new window

See attached example
Example.xlsm
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now