transposing data

**JoeK** · 10-20-2011, 05:17 PM

I have data that looks like...

1 2 3 4 5 6
7 8 9 10 11 12
13 14 15 16 17 18

I want it all in one column like....

1
2
3
4
5
6
7
8 etc How do I do this? Thanks.

**tom1977** · 10-20-2011, 05:20 PM

every number in separate cells or every number from one row in one cell?

**Azam Ali** · 10-21-2011, 01:48 AM

see attatchment

**tom1977** · 10-21-2011, 06:15 AM

You can use such code:

Sub aa()
Dim myrange As Range
Dim os As Integer
Dim ka As Variant
Columns("h").ClearContents
Set myrange = Cells(1, 1).CurrentRegion
myrange.Select
os = Cells(Rows.Count, "H").End(xlUp).Row
For Each ka In myrange
If Not IsEmpty(ka.Value) Then
os = os + 1
Range("h" & os - 1) = ka.Value
End If
Next
End Sub

**JoeK** · 10-21-2011, 08:15 AM

Thanks everybody. The numbers are in different cells. Azam your formula works if the numbers range from low to high. However, actually the numbers are a bit random (high then low then high etc). Your formula seems to work only if the numbers range from low to high. So in actuality the numbers are 1 to 24 across (hours) then 1 to 365 days down. I want one column 1 to 24 for all 365 days. I am an excel novice and your help is much appreciated. So I now have
1 5 10 4 6 9 10
5 12 15 89 3 55
9 11 3 10 12 1

I want it all in one column like....

1
5
10
4
6
9
10
5
12
15
89
3
etc
How do I do this? Thanks.