Convert Row into Column

Question

I have the following data in excel:-

I need to convert them into row & the final result should be as follows:-

How can I do it in excel?

you can use Pivot to get this done. Or you can also use transpose function. — Karpak, Jun 22 '17 at 05:37
Possible duplicate of [Transpose a range in VBA](https://stackoverflow.com/questions/13174916/transpose-a-range-in-vba) — miroxlav, Jun 22 '17 at 05:38
Transpose works well if it is just a couple of row. I have hundreds of row, so copy & paste (& transpose) doesn't quite fit. I'm looking at VBA. — Bob, Jun 22 '17 at 05:49
Possible duplicate of [Transpose a range row](https://stackoverflow.com/questions/43619102/transpose-a-range-row) — David Brossard, Jun 22 '17 at 08:05

Dy.Lee · Accepted Answer · 2017-06-22T08:03:29.337

3

This code New sheet add and out the result.

Sub test()
    Dim vDB, vR()
    Dim Ws As Worksheet
    Dim r As Long, c As Long
    Dim i As Long, n As Long, j As Integer
    Set Ws = ActiveSheet

    With Ws
        r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        'vDB = .Range("a1", .Cells(r, c))
        vDB = .Range("b1", "j" & .Range("d" & Rows.Count).End(xlUp).Row)
    End With

    For i = 2 To UBound(vDB, 1) Step 4
        For j = 4 To UBound(vDB, 2)
            n = n + 1
            ReDim Preserve vR(1 To 7, 1 To n)
            vR(1, n) = vDB(i, 1)
            vR(2, n) = vDB(i, 2)
            vR(3, n) = vDB(i, j)
            vR(4, n) = vDB(i + 1, j)
            vR(5, n) = vDB(i + 2, j)
            vR(6, n) = vDB(i + 3, j)
            vR(7, n) = vDB(1, j)
        Next j
    Next i
    Sheets.Add
    Range("a1").Resize(1, 7) = Array("Managed", "Type", "On/Off Hire", "Customer", "Locaton", "Daily Opex", "Date")
    Range("a2").Resize(n, 7) = WorksheetFunction.Transpose(vR)


End Sub

Case of including Support

Sub test()
    Dim vDB, vR()
    Dim Ws As Worksheet
    Dim r As Long, c As Long
    Dim i As Long, n As Long, j As Integer
    Set Ws = ActiveSheet

    With Ws
        r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        'vDB = .Range("a1", .Cells(r, c))
        vDB = .Range("b1", "j" & .Range("d" & Rows.Count).End(xlUp).Row)
    End With

    For i = 2 To UBound(vDB, 1) Step 5
        For j = 4 To UBound(vDB, 2)
            n = n + 1
            ReDim Preserve vR(1 To 8, 1 To n)
            vR(1, n) = vDB(i, 1)
            vR(2, n) = vDB(i, 2)
            vR(3, n) = vDB(i, j)
            vR(4, n) = vDB(i + 1, j)
            vR(5, n) = vDB(i + 2, j)
            vR(6, n) = vDB(i + 3, j)
            vR(7, n) = vDB(i + 4, j)
            vR(8, n) = vDB(1, j)
        Next j
    Next i
    Sheets.Add
    Range("a1").Resize(1, 8) = Array("Managed", "Type", "On/Off Hire", "Customer", "Locaton", "Support", "Daily Opex", "Date")
    Range("a2").Resize(n, 8) = WorksheetFunction.Transpose(vR)


End Sub

edited Jun 22 '17 at 08:03

answered Jun 22 '17 at 06:10

Dy.Lee

7,527
1
12
14

Tried but the "Date" seems to be incorrect. Instead of 1st June 2017, it shows as 6th January 2017. – Bob Jun 22 '17 at 07:02
i mistake. vDB = .Range("a1", .Cells(r, c)) change vDB = .Range("b1", .Cells(r, c)) – Dy.Lee Jun 22 '17 at 07:37
Changed but when i tried to run, it prompted a "Run-time error '9': Subscription out of range". Debug => vR(3, n) = vDB(i, j) – Bob Jun 22 '17 at 07:44
Also, i added an additional row known as "Support". Which parameters should i amend? – Bob Jun 22 '17 at 07:51
Just tried. 2 problems. The Date is still reflect as 6th Feb 2017, 6th Mar 2017 etc. The data doesn't start with 1 June instead it begins with 2 June. – Bob Jun 22 '17 at 08:17
In addition to that, it stops at 7th June. – Bob Jun 22 '17 at 08:24
your column end is " j" column or else? – Dy.Lee Jun 22 '17 at 13:42
I found out the reason why it starts with 2 June instead of 1st June. Just amend this to vDB = .Range("a1", "j" & .Range("d" & Rows.Count).End(xlUp).Row) – Bob Jun 23 '17 at 00:47
Still having the date format problem. Instead of 01-06-17 (as in 1st June 2017), it shows as 06-01-17 (as in 6th Jan 2017). – Bob Jun 23 '17 at 00:53
@Bob: just add last line. Columns(8).NumberFormatLocal = "dd-mm-yyyy" – Dy.Lee Jun 23 '17 at 04:05
I added Columns(8).NumberFormatLocal = "dd-mm-yyyy" just before End Sub & the result is still the same. – Bob Jun 23 '17 at 05:18
@ Dy.Lee. Thanks for your help. I do really appreciate it. I hv added another column known as Group. Which parameters should i edit? – Bob Jun 23 '17 at 06:13
I managed to do it. Edit "For j = 4 To UBound(vDB, 2)" to "For j = 5 To UBound(vDB, 2)". Thanks a lot. – Bob Jun 23 '17 at 08:07

Convert Row into Column

1 Answers1