Reshape- Range (NRx7C) into ("N/3"Rx "3x7"C)

[Knightrider]

Good Morning all,

I am new in the forum. I am struggling to reshape a range, what I have tried with formulas but without success. I need to convert a range of NRx7C ( N = number of rows , C = number of columns ) which is constant in columns but not in rows (see attacthment "Source"). At the end of the process, the range shall be reshaped as it is shown in Attachment "Result". I only have in this example one range of data, but I would need to include this routine into another code in which I am working now, since this request is part of the problem. The sequence to offset the range is as per Source:

- First range 1Rx7C remain in same place
- Second range offset -1 row up and 7column right
- Third range offset -1 row up and 14 column to right
- Fourth range as first one, remain in same place
- Fifth range , ( as second range offset)
- Sixth range , ( as third range offset )

So, given this range (B3:H41) , at the end shall be (B3:V15) form. (See result sheet)

any ideas?

I have uploaded the excel sheet if is easier for you to visualize the request. I

https://www.dropbox.com/s/hfl1lkp6uqks1dq/Example_rev0.xlsm?dl=0

Thank you very much in advance,

With best Regards,

Miguel,

 

[Fluff]

Hi & welcome to the board.
Is this what you want

Sub Rearrange()
   Dim ary As Variant
   Dim Nary() As Variant
   Dim i As Long, j As Long, k As Long, l As Long
   k = 1
   ary = Sheets("Source").Range("B3", Sheets("Source").Range("H" & Rows.Count).End(xlUp))
   ReDim Nary(1 To UBound(ary) / 3, 1 To 21)
   For i = 1 To UBound(Nary)
       l = 1
      For j = 1 To 21
         Nary(i, j) = ary(k, l)
        l = l + 1
         If l = 8 Then l = 1: k = k + 1
      Next j
   Next i
   Sheets("test").Range("B3:V1048576").ClearContents
   Sheets("test").Range("B3").Resize(UBound(Nary), 21).Value = Nary
End Sub

 

[Knightrider]

Good Morning Fluff,

I have tested your Code and it works only when the following conditions meet:
- The number of ranges to Offset is greater than 3
- The number of ranges to Offset is multiple of 3.

In the rest of cases, when I have only 3 ranges or less, or I have 4,5,7,8,10,11,13.... and so on number of ranges, the macro only Offset multiple of three ranges.

Could it be solved?

Alternatively I thought about the idea to copy and paste the range into the next to nneighbouring set of 7columns range, as per the attachment, and then if we remove two rows every three, it also works, please find the example also attached under the following Link. Maybe, this would be easier to make with VBA but in any case I Need to identify for each column of Nx7C ranges, the starting cell and the last, and then copy and paste three two more times, everytime starting from the second range in the column set. I have attached the examplle in source 2 for a better understanding.

​

https://www.dropbox.com/s/256iqzv4pc225yp/Example_rev1.xlsm?dl=0

Thank you very much!​

 

[Fluff]

The reason the code splits the data into 3 columns, is because that's what you asked for in the op.
If that that is not what you actually want. Could you please clearly explain what you do want.