Loop, Copy and Transpose

**VBA Noob** · 02-21-2008, 07:34 PM

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** · 02-21-2008, 07:49 PM

Thank you very much for your insight.

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

**VBA Noob** · 02-21-2008, 07:54 PM

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** · 02-21-2008, 08:08 PM

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

**VBA Noob** · 02-21-2008, 08:17 PM

If column H is empty then the below will return the offset by 1 row of H1 which is H2. If you change it to 2 then it will return H3 e.g two rows etc

So the larger the offset number the larger gap between rows