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

[Sonova]

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)

[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

A	B	C	D	E	F	G	H
1	2	3	4	5	6	7	8
5	6	7	8

<tbody>
</tbody>

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)

A	B	C	D	E	F	G	H	I	J	K	L
1	2	3	4	5	6	7	8	17	18	19	20
9	10	11	12	13	14	15	16	21	22	23	24
5	6	7	8
13	14	15	16
17	18	19	20
21	22	23	24

<tbody>
</tbody>

 

[MickG]

Try this for results starting "F1" for range "F1:MY61".

[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

 

[Sonova]

Try this for results starting "F1" for range "F1:MY61".

[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

Thanks Mike,I tried to run the code but there was no result.

 

[Sonova]

Thanks Mike,I tried to run the code but there was no result.

Mike apologies I had an error when i copied the code, it seems to work fine, thank you for your help.

 

[MickG]

You're welcome