Hi Forum,

I would be very grateful if anyone could offer any help with regards to
using VBA to automate the following task;

I have one column (A) of numerical data in ascending order that changes in
size from day to day (which is why recording a macro is not best suited). I
would like to use code to automate the way the data could be displayed in
columns moving left to right 6 rows deep whilst maintaining its ascendancy.
The following is a simplified example;

A

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

Ideally would become;

A B C D E

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

As always any help would be greatly appreciated
Many Thanks
Matt

Sub MakeColumns()

StartRow = 1
RowCount = StartRow + 6
DestRow = StartRow
ColCount = 2
Count = 1
Do While Range("A" & RowCount) < ""
Cells(DestRow, ColCount) = Range("A" & RowCount)
If Count = 6 Then
Count = 1
DestRow = StartRow
ColCount = ColCount + 1
Else
Count = Count + 1
DestRow = DestRow + 1
End If
RowCount = RowCount + 1
Loop
Range("A" & (StartRow + 6) & ":A" & (RowCount - 1)).Delete

End Sub


"matt3542" wrote:

Hi Forum,

I would be very grateful if anyone could offer any help with regards to
using VBA to automate the following task;

I have one column (A) of numerical data in ascending order that changes in
size from day to day (which is why recording a macro is not best suited). I
would like to use code to automate the way the data could be displayed in
columns moving left to right 6 rows deep whilst maintaining its ascendancy.
The following is a simplified example;

A

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

Ideally would become;

A B C D E

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

As always any help would be greatly appreciated
Many Thanks
Matt

Thankyou Joel, that was incredibly helpful and worked like a dream!

Regards
Matt

"Joel" wrote:

Sub MakeColumns()

StartRow = 1
RowCount = StartRow + 6
DestRow = StartRow
ColCount = 2
Count = 1
Do While Range("A" & RowCount) < ""
Cells(DestRow, ColCount) = Range("A" & RowCount)
If Count = 6 Then
Count = 1
DestRow = StartRow
ColCount = ColCount + 1
Else
Count = Count + 1
DestRow = DestRow + 1
End If
RowCount = RowCount + 1
Loop
Range("A" & (StartRow + 6) & ":A" & (RowCount - 1)).Delete

End Sub


"matt3542" wrote:

Hi Forum,

I would be very grateful if anyone could offer any help with regards to
using VBA to automate the following task;

I have one column (A) of numerical data in ascending order that changes in
size from day to day (which is why recording a macro is not best suited). I
would like to use code to automate the way the data could be displayed in
columns moving left to right 6 rows deep whilst maintaining its ascendancy.
The following is a simplified example;

A

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

Ideally would become;

A B C D E

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

As always any help would be greatly appreciated
Many Thanks
Matt