# Rearrange Table Data

#### Carin

##### Board Regular
I have 14 tables merged into one large table which I would like to move into one column.
Table 1 = B1:M5
Table 2 = B6:M10
Table 3 = B11:M15
etc.
I would like a code to move
B1:B5 => A1:A5
C1:C5 => A6:A11
D1:D5 => A12:A17
etc., ending at A60 - then wrap down to
B6:B10 => A61:A65
just continue moving five rows of data from B to M and once that data has been moved, move down and grab the next five rows of data.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### VoG

##### Legend
Try this - it copies rather than moves so you can check if it works. If it does, it can be amended to move.

Code:
``````Sub atest()
Dim i As Integer, j As Integer, k As Integer
k = 1
For i = 1 To 11 Step 5
For j = 2 To 13
Cells(i, j).Resize(5).Copy Destination:=Cells(k, 1)
k = k + 5
Next j
Next i
End Sub``````

#### Carin

##### Board Regular
Works like a charm!

#### VoG

##### Legend
Excellent! This modification will move rather than copy:

Rich (BB code):
``````Sub atest()
Dim i As Integer, j As Integer, k As Integer
k = 1
For i = 1 To 11 Step 5
For j = 2 To 13
Cells(i, j).Resize(5).Cut Destination:=Cells(k, 1)
k = k + 5
Next j
Next i
End Sub``````

#### Carin

##### Board Regular
Thanks so much...this works for three of my groups...how do I get it to work for all 14 groups?

#### VoG

##### Legend
Assuming that the complete range is B1:M70 try

Code:
``````Sub atest()
Dim i As Integer, j As Integer, k As Integer
k = 1
For i = 1 To 66 Step 5
For j = 2 To 13
Cells(i, j).Resize(5).Cut Destination:=Cells(k, 1)
k = k + 5
Next j
Next i
End Sub``````

Replies
1
Views
134
Replies
0
Views
695
Replies
1
Views
511
Replies
1
Views
133
Replies
13
Views
940

1,191,576
Messages
5,987,390
Members
440,095
Latest member
yanaungmyint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back