Copying Rows of Data to Single Column for Chart

Posted by Bruce J on January 14, 2001 6:03 PM

I am looking to create a column of data to plot. The
existing format of my data is one day per row, with 24 columns of
hourly data columns per day. I would like to be able
to copy the daily hourly data to a single column for
plotting a month's or even year's worth of data.

Here's a shortened example of what I would like to do:

Data:
A B C D E F
1 2 3 4 5 6
K L M O P Q
0 9 8 7 6 5

Would like to have the following column of data:
A
B
C
D
E
F
1
2
3
4
5
6
K
L
M
N
O
P
Q
0
9
8
7
6
5

Is there a fairly easy macro to be able to do this?

Thank you.

-- Bruce J

Posted by Dave Hawley on January 14, 2001 8:03 PM

Hi Bruce

Try this one, should do the trick.

Sub RowsToSingleColumn()
Dim Reply As Range
Dim cell As Range
Dim RowCount As Integer
On Error Resume Next
Set Reply = Application.InputBox _
("Select your data", "OzGrid.Com", _
Selection.CurrentRegion.Address, Type:=8)
If Reply Is Nothing Then Exit Sub

Application.ScreenUpdating = False
With Reply
.Columns(1).EntireColumn.Insert
.Columns(1).Offset(0, -1).Cells(1, 1) = "Transposed"
RowCount = .Rows.Count

For Each cell In .Rows
cell.Rows(1).Copy
.Columns(1).Offset(0, -1) _
.Range("A65536").End(xlUp).Offset(1, 0) _
.PasteSpecial Transpose:=True

Next
End With
Application.ScreenUpdating = True
End Sub

Good luck

Dave

OzGrid Business Applications