Transposing each set of 22 items (all from the same column) into rows

[Kiba]

Hello. I need help transposing a huge amount of data in excel through the use of vba. All of it is in one column (M) and I need to transpose every 22 values into its own row. The entire column has 7282 values or goes down to M7282. These rows need to start in column O. So M1-M22 would be transposed and would start in O1, M23-M44 would be transposed and would start in O2, etc. Help would be greatly appreciated as I have multiple spreadsheets that I would need to use this macro on.



Thanks,
Kiba

[SamT]

Option Explicit

Sub Transposer22()
Const MCol As Long = 13
Dim Ocel As Range
Dim i As Long
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(Rows.Count, "M").End(xlUp).Row
Set Ocel = .Range("O1")

With .Columns(MCol)
For i = 1 To LastRow Step 22
.Cells(i).Resize(22, 0).Copy
Ocel.PasteSpecial , , , Transpose
Set Ocel = Ocel.Offset(1)
Next i
End With
End With

End Sub

[Kiba]

Thank you so much for responding to me so fast!

When I try to run the macro it gives me "compile error: Variable not defined" and it selects Transpose from this line of code : Ocel.PasteSpecial , , , Transpose . It also highlights Sub Transposer22() after I click okay.

Do I need to define Transpose up near MCol, Ocel, i, and LastRow? If so then what data type would it be?

Thanks,
Kiba

[p45cal]

Ocel.PasteSpecial , , , Transpose
should probably be:
Ocel.PasteSpecial , , , True

edit post trying it out:
You can't have .Resize(22,0) - that would be a column zero cells wide, it needs to be
.Resize(22,1) or more simply .Resize(22) :

.Cells(i).Resize(22).Copy

[Kiba]

Thank you! Those two bug fixes made the code work properly.

As a side note, what function does this line of code have: Const MCol As Long = 13 (where does the 13 come from?)

I'm just wondering in case I need to alter this code in the future / transpose something else that is slightly different.



Thanks again to both of you for helping me out!

Kiba

[SamT]

Column "M" is the 13th column.

With MCol
'...
.Cells(i)

returns the i'th cell down in column M

[Kiba]

Makes sense. Thanks again!

I'll mark the thread as solved now!

[jolivanes]

I know it has been marked as solved but this might be slightly faster on a large range.

Sub Kiba()
Dim lr As Long, j As Long, i As Long
lr = Cells(Rows.Count, 13).End(xlUp).Row
j = 1
For i = 1 To WorksheetFunction.RoundUp(lr / 22, 0)
    Cells(i, 15).Resize(, 22).Value = WorksheetFunction.Transpose(Cells(j, 13).Resize(22))
    j = j + 22
Next i
End Sub

[Paul_Hossler]

Since I was working on it anyways, just throwing out another option that uses internal arrays for speed to rearrange

Option Explicit

Sub test()
    Const cSrc As String = "M1"
    Const cDest As String = "O1"
    Const cWidth As Long = 22
    
    Dim rSrc As Range
    Dim V1 As Variant, V2() As Variant
    Dim r As Long, c As Long
    
    Set rSrc = Range(Range(cSrc), Range(cSrc).End(xlDown))
    
    V1 = Application.WorksheetFunction.Transpose(rSrc.Value)
    ReDim Preserve V2(0 To (UBound(V1) / cWidth) - 1, 0 To cWidth - 1)
    
    For r = LBound(V1) - 1 To UBound(V1) - 1
        V2(r \ cWidth, r Mod cWidth) = V1(r + 1)
    Next r
    Range(cDest).Resize(UBound(V2, 1) + 1, UBound(V2, 2) + 1).Value = V2

End Sub

[jolivanes]

@ Paul
Might need adjusting if the division is not exact.

[Kiba]

Wow, this is my first thread on this forum and everyone is so helpful! Thanks Jolivanes and Paul_Hossler for additional solutions!

Kiba

[Paul_Hossler]

@ Paul
Might need adjusting if the division is not exact.

Oh, there's many other ways it could fail. That's just one

BTW, if all of the elements are the same and standard VBA variable types (Long, Double, etc.), there's much faster ways than looping in arrays