Move Data in Columns with X number of Rows to an Array

S

Sonova

New Member
Joined
Feb 21, 2017
Messages
24
Hi

I have been working on this for quite some time and have somewhat of a solution but I am also not sure if this is the best approach.

I have a large data-set with more than a 100 columns(AF:MY) and the data is structured with 61 rows of data for each column.

What I need to do is copy and paste all of the columns and rows after column AF into an array of 4 columns.

What I have is this code. ( I have read up on arrays but think I am getting confused with multidimensional arrays)

Code: 
[COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] TransformToTable[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Arr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336])[/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]

    ColIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] Iter1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] Iter2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336]
            Arr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Iter1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Iter2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] ColIndex[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value
            ColIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ColIndex [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1 [/COLOR][COLOR=#101094]To [/COLOR][COLOR=#7d2727]4[/COLOR][COLOR=#303336]
            Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Arr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
 [/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR]


Illustration of Code above

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I am trying to do is loop through the columns and rows of the required data and copy and paste into the required number of columns in this case 4. In the below example it will loop for the 61 rows of data in columns E, F,G,H and then move on to columns I J K L. My code gets stuck at only the first row ie will copy and paste 5-8 & 17-20 but not 13-24 .


This is what I am trying to do. ( each column has 61 rows)

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Try this for results starting "F1" for range "F1:MY61".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG28Feb34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Variant, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
Ray = Range("AF1:MY61")
ReDim nRay(1 To 5063, 1 To 4)
[COLOR="Navy"]For[/COLOR] Col = 1 To 332 [COLOR="Navy"]Step[/COLOR] 4
   [COLOR="Navy"]For[/COLOR] Rw = 1 To 61
          c = c + 1
        nRay(c, 1) = Ray(Rw, Col)
        nRay(c, 2) = Ray(Rw, Col + 1)
        nRay(c, 3) = Ray(Rw, Col + 2)
        nRay(c, 4) = Ray(Rw, Col + 3)
    [COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Next[/COLOR] Col
Range("AF1").Resize(5063, 4).Value = nRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this for results starting "F1" for range "F1:MY61".
Code: 
[COLOR=Navy]Sub[/COLOR] MG28Feb34
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Variant, Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant
Ray = Range("AF1:MY61")
ReDim nRay(1 To 5063, 1 To 4)
[COLOR=Navy]For[/COLOR] Col = 1 To 332 [COLOR=Navy]Step[/COLOR] 4
   [COLOR=Navy]For[/COLOR] Rw = 1 To 61
          c = c + 1
        nRay(c, 1) = Ray(Rw, Col)
        nRay(c, 2) = Ray(Rw, Col + 1)
        nRay(c, 3) = Ray(Rw, Col + 2)
        nRay(c, 4) = Ray(Rw, Col + 3)
    [COLOR=Navy]Next[/COLOR] Rw
[COLOR=Navy]Next[/COLOR] Col
Range("AF1").Resize(5063, 4).Value = nRay
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...


Thanks Mike,I tried to run the code but there was no result.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Filter function returning zero rows. Assigning results to an array.
Replies
4
Views
152
RoryA
RoryA
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
96
Skyybot
Skyybot
S
  • Solved
Not copying first Column of table
Replies
2
Views
433
sean1541
S
M
  • Question Question
Compiling and then splitting an array (array formula or VBA)
Replies
4
Views
318
Matrovsky
M
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
386
Peter_SSs
Peter_SSs

Share this page

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?

Disable AdBlock

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
Back
Top