VBA macro HELP- Column to rows

[kmm992]

Hi there!

I am in need of some VBA macro magic here. I have a stream of data that represents energy usage on an hourly basis. It is in one column currently that goes Day 1 HE 1, Day 1 HE 2, etc.

I would like to have a macro that takes the 24 hours and transposes it into a table that has the days of the month on the right going down and the data in rows with hours 1-24 being the top header.

Basically it would be a macro that transposes the 24 hourly data points for each day 31 times from a single column to a series of rows

from this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Hour[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]8756[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]425[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]425[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4345[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]786[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]78453[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]7532[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]735[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]735[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]753[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]753[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]753[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]735[/TD]
[/TR]
</tbody>[/TABLE]

To this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]he[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]day[/TD]
[TD]5646[/TD]
[TD]564[/TD]
[TD]654[/TD]
[TD]5646[/TD]
[TD]645[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]456[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]54[/TD]
[TD]456[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[TD]546[/TD]
[/TR]
</tbody>[/TABLE]


I realize the numbers dont match just wanted to show an example. This would be done for the whole month in this case with the days going down in rows.

I would very much appreciate the help!

 

[Jason Brown]

https://www.youtube.com/watch?v=YLxnmyYCis8

This guy talks about transposing data from horizontal to vertical, in addition to writing from one workbook to another.

While I'm not the best, after the DIM and selects looks like you'll use .offset to transpose them. Increasing numbers will move it along.

 

[offthelip]

this code should do it for you:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow + 1, 2))
Range(Cells(1, 3), Cells(32, 28)) = ""
outarr = Range(Cells(1, 3), Cells(32, 28))
ci = 1
ri = 2
For i = 2 To lastrow
   outarr(1, ci) = inarr(i, 1)
   outarr(ri, ci) = inarr(i, 2)
   ci = ci + 1
   If inarr(i + 1, 1) < inarr(i, 1) Then
    ri = ri + 1
    ci = 1
   End If
   
 Next i
Range(Cells(1, 3), Cells(32, 28)) = outarr
 
 End Sub