Excel 2010: Move cells from vertical data to horizontal with VBA

Question

Excel 2010

I want to move the following data from it's vertical state to horizontal data. I would like a solution in VBA please. (I already have a formula).

Order = (A10) Result = (B10) runs over 1000 rows

| Order1         | result                                                                       
| line1          | result 1
| line2          | result 1
| line3          | result 1
| line4          | result 1
| line5          | result 1
| line6          | result 1
| line7          | result 1
| line8          | result 1
|      br        |                                                                           
| Order2         | result                                                                   
| line1          | result 1
| line2          | result 1
| line3          | result 1
| line4          | result 1
| line5          | result 1
| line6          | result 1
| line7          | result 1
| line8          | result 1

I want it to resolve as:

Order 1  |  result1  |  result2  |  result3  |  result4  |  result5  |  result6  |  result7  |  result8  |  
Order 2  |  result1  |  result2  |  result3  |  result4  |  result5  |  result6  |  result7  |  result8  |

Thanks in advance

EDIT
My current formula is this: (C10) =IF(A3="Order1 ",1,0) (result: 1)
(D10) =IF($C3=1,B3,0) (result: result from line1)
(E10) =IF($C3=1,B10,0) (result: result from line2)
and so on.

I then copy and autofill the entire sheet of data and it fills it all in. And I build the new table this way.

When I macro record it doesn't record the actual formulas that I have in the cells.

Most of the transpose answers resolve both columns to single lines. I need to take it from the pivot (ie Order1) then moving just the results to their new columns. As I pointed out, I do have a formula that does it, however I would like it in VBA. I've been looking at this for days. — MrsAdmin
Excel can transpose the table using "copy" and "paste special…". These two commands should also be available in VBA — cello

Gary's Student Gary's Student · Accepted Answer · 2015-02-11T16:16:30

If we start with:

enter image description here

with a blank between orders in Sheet1, then this macro:

Sub reorg()
    Dim s1 As Worksheet, s2 As Worksheet, N As Long, i As Long, j As Long, k As Long
    Dim v As Variant
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    N = s1.Cells(Rows.Count, "A").End(xlUp).Row
    j = 1
    k = 1

    For i = 1 To N
        v = s1.Cells(i, 1).Value
        If v = "" Then
            j = j + 1
            k = 1
        Else
            s2.Cells(j, k) = v
            k = k + 1
        End If
    Next i
End Sub

will produce this in Sheet2

enter image description here

EDIT#1:

To use A10 as both the start and destination, use:

Sub reorg()
    Dim s1 As Worksheet, s2 As Worksheet, N As Long, i As Long, j As Long, k As Long
    Dim v As Variant
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    N = s1.Cells(Rows.Count, "A").End(xlUp).Row
    j = 10
    k = 1

    For i = 10 To N
        v = s1.Cells(i, 1).Value
        If v = "" Then
            j = j + 1
            k = 1
        Else
            s2.Cells(j, k) = v
            k = k + 1
        End If
    Next i
End Sub

Excel 2010: Move cells from vertical data to horizontal with VBA

1 Answers