VBA to seperate and transpose data into rows

Asked Jun 22 '18 at 15:13

Active Jun 22 '18 at 15:19

Viewed 36 times

I've been trying to figure out how I can separate two columns of data on a worksheet into rows, using the following format.

From:

To:

I was able to figure out a portion of this (seperating and transposing the names in Column B) from another post (linked below), but I can't figure out how to copy down the data in column A

Sub Vertical()
Dim i As Long, st As String
i = 1
Dim startP As Range
For Each r In Selection
    If i = 1 Then
        st = r.Text
        i = 2
    Else
        st = st & "," & r.Text
    End If
Next r

Set startP = Selection(1, 2)
ary = Split(st, ",")
i = 1
For Each a In ary
    startP(i, 1).Value = a
    i = i + 1
Next a  
End Sub

Of course, the actual data is a long list of workspaces within our system and employees who work them. I just couldn't post that here.

Thank you, in advance!!!

edited Jun 22 '18 at 15:19

dwirony

5,487
3
21
43

asked Jun 22 '18 at 15:13

Kristopher Penland

1

See: https://stackoverflow.com/questions/41003866/excel-macro-split-comma-separated-entries-to-new-rows – Scott Craner Jun 22 '18 at 15:15
Wow, thank you! With a slight adjustment to the code that resolves my issue perfectly. I couldn't find that in my searches, how in the world did you find something so quick! – Kristopher Penland Jun 22 '18 at 15:31
1

@KristopherPenland Scott's been around the block a few times :) – dwirony Jun 22 '18 at 16:09

VBA to seperate and transpose data into rows

0 Answers0