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:

Hour	data
1	8756
2	425
3	452
4	452
5	425
6	4345
7	453
8	453
9	453
10	453
11	453
12	453
13	453
14	453
15	786
17	78453
18	7532
19	735
20	735
21	753
22	753
23	753
24	735

<tbody>
</tbody>

To this:

he	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
day	5646	564	654	5646	645	546	546	546	546	546	546	546	546	456	546	546	546	546	54	456	546	546	546	546

<tbody>
</tbody>

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