View Full Version : [SOLVED] Transposing each set of 22 items (all from the same column) into rows

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

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

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

11-09-2016, 09:40 AM

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

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

Column "M" is the 13th column.

With MCol

'...

.Cells(i)

returns the i'th cell down in column M

Makes sense. Thanks again!

I'll mark the thread as solved now!

jolivanes

11-10-2016, 08:10 AM

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

11-10-2016, 08:50 AM

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

11-10-2016, 10:34 AM

@ Paul

Might need adjusting if the division is not exact.

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

11-10-2016, 01:18 PM

@ 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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.