Using VBA to sort a column of data

[matt3542]

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 a 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

 

[Otto Moehrbach]

Matt
What you describe is called Snaking a column.
Try this macro and see if it works for you. I assumed your initial data
is in Column A starting in row 1. If your data starts in some other row,
find the line that says "c=1" and change the "1" to your starting row
number.. Post back if you need more. HTH Otto
Sub ReFormat()
Dim iTarget As Long
Dim c As Long
iTarget = 1
c = 1
Application.ScreenUpdating = False
Do
Cells(c, 1).Resize(6).Cut Destination:=Cells(1, iTarget)
c = c + 6
iTarget = iTarget + 1
Loop Until IsEmpty(Cells(c, 1).Value)
Application.ScreenUpdating = True
End Sub

 

[matt3542]

Hi Otto, apologies for the delay replying, many thanks for taking the time to
offer a solution to my problem. I have just tried the code and it works
beautifully!

Best Regards
Matt