Transpose every X rows to columns (Automation)

[griffindor2020]

Hello,

I would like to transpose a range of data (A1 to KJ1) from every second row (row 1, row 3, row 5, until the last row) under one single column (e.g. column C) in the same sheet.

I am having difficulty creating a macro with a loop that will help me do this. I tried following some youtube tutorials online, but it is not working out very well for me.

It says that there is a compile error : argument not optional.

I have thousands of rows to transpose into one single column.

Please see my script below.

 

[griffindor2020]

For the even row numbers change this:

If i Mod 2 <> 0 Then
to this:

If i Mod 2 = 0 Then

If you would like to put the even rows in the column following the odd rows, change this

Range("KK" & NxRw).Resize(UBound(Vrw, 2), 1).Value = Vout

to this:

Range("KL" & NxRw).Resize(UBound(Vrw, 2), 1).Value = Vout

As is, the code does not ignore blank cells but they can be removed like this:

Sub DataTranspose()
Dim R As Range, Vin As Variant, Vrw As Variant, Vout As Variant, i As Long, NxRw As Long
Set R = Range("A1").CurrentRegion
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
Application.ScreenUpdating = False
For i = 1 To UBound(Vin, 1)
    If i Mod 2 <> 0 Then
        Vrw = R.Rows(i).Value
        Vout = Application.Transpose(Vrw)
        NxRw = IIf(IsEmpty(Range("KK1")), 1, Range("KK" & Rows.Count).End(xlUp).Row + 1)
        Range("KK" & NxRw).Resize(UBound(Vrw, 2), 1).Value = Vout
    End If
Next i
With Columns("KK")
    On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    On Error GoTo 0
    .AutoFit
End With
Application.ScreenUpdating = True
End Sub

Hello,

For some reason, this script isn't working as well for me as the first one, but I can definitely say the problem is solved for now !

I was able to organize my data in a way that makes sense to me with the first script and the additional advice you have me e.g (switching <> for =) and the column where the data is supposed to appear.

By the way, I was wondering if there are any classes that teach us this kind of coding/data manipulation? I've been following some online classes for excel but it's very elementary. If you have any ideas of classes that I could take to give me a better sense of coding I am open for suggestions.

Otherwise I will be using this forum again. It's very helpful !

Thank you @JoeMo and @Fluff for being part of this thread. I was struggling to figure this out for days and you came to my rescue in a couple of hours

Wishing you both well,

-griffindor2020

 

[JoeMo]

Hello,

For some reason, this script isn't working as well for me as the first one, but I can definitely say the problem is solved for now !

I was able to organize my data in a way that makes sense to me with the first script and the additional advice you have me e.g (switching <> for =) and the column where the data is supposed to appear.

By the way, I was wondering if there are any classes that teach us this kind of coding/data manipulation? I've been following some online classes for excel but it's very elementary. If you have any ideas of classes that I could take to give me a better sense of coding I am open for suggestions.

Otherwise I will be using this forum again. It's very helpful !

Thank you @JoeMo and @Fluff for being part of this thread. I was struggling to figure this out for days and you came to my rescue in a couple of hours

Wishing you both well,

-griffindor2020

You are welcome - glad we could help. If you care to elaborate on what isn't "working as well" in the script I posted in post #10, I might be able to resolve your issues.

 

[griffindor2020]

Good morning ! @JoeMo

Thank you for getting in touch with me again.

What I meant by not "working as well" was that when I tried running the code it just would not run on my end.

So instead I just used the first one and that one worked great for me !

-griffindor2020