Loop, Copy and Transpose

[jmicdk]

Hi, I'm hoping someone can help me out with the following.

I have data in Col B (+- 9000 rows) and want to copy four cells (starting in "B3:B6") and transpose it in "H2".
Then find the next 4 cells in col B, copy and transpose this in "H3" and so on

I need to repeat this till the first empty cell in Col B.

Unfortunately, i'm struggling with loops and am hoping someone can help.

Thanks in advance.

[shg]

How about just a worksheet function solution?

[VBA Noob]

Try

Sub Transposer()
Dim Lr As Long, i As Long
Lr = Cells(Rows.Count, "B").End(xlUp).Row

Application.ScreenUpdating = False
    For i = 3 To Lr Step 4
        Range("B" & i, "B" & i + 3).Copy
        Range("H65536").End(xlUp).Offset(1). _
        PasteSpecial Transpose:=True
    Next i
    
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

End Sub

VBA Noob

[jmicdk]

Thank you very much for your insight.

What does "Step 4" imply in this instance. Thank you

[VBA Noob]

i starts from 3 to Lastrow in Col B. The step 4 jumps it to 7.

So the first i refers to B3 to B6 (i=3 + 3)
the 2nd refers to B7 to B10 etc

Range("B" & i, "B" & i + 3).

HTH

VBA Noob

[jmicdk]

Thanks again. One last question...

If i change Offset(1) to Offset(2) then it creates an empty row btw each row that was pasted.

Range("H65536").End(xlUp).Offset(1).PasteSpecial Transpose:=True

Is this just a indication of the destination to whihc it is copied, so if i change it to Offset(2) then it should only move to pasted data on row down. Which it does But it also inserts an empty row btw each pasted row.

Thanks for you time