PDA

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



Kiba
11-08-2016, 07:23 PM
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
11-08-2016, 09:42 PM
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
11-09-2016, 08:51 AM
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

Kiba
11-09-2016, 01:02 PM
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
11-09-2016, 02:02 PM
Column "M" is the 13th column.


With MCol
'...
.Cells(i)
returns the i'th cell down in column M

Kiba
11-09-2016, 06:15 PM
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.

Kiba
11-10-2016, 12:48 PM
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