Transpose every X rows to columns (Automation)

griffindor2020

New Member
Joined
Jun 6, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. MacOS
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. :)
 

Attachments

  • Screen Shot 2020-06-06 at 10.49.39 AM.png
    Screen Shot 2020-06-06 at 10.49.39 AM.png
    44.2 KB · Views: 11

griffindor2020

New Member
Joined
Jun 6, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. MacOS
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:
VBA Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 6, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,530
Messages
5,548,583
Members
410,854
Latest member
ajbaluyut
Top