Please need help with Transpose,offset,loop -Specially the loop-

F

frankyalta

New Member
Joined
Nov 8, 2015
Messages
29
Hi Friends,

I have a VBA code that transpose the Column A and B, (everything perfect), But now i'm adding more columns and i need to do the same Transpose but with offset 1 but my code not have a Loop till the last empty column.

Can someone tell me where can i insert the Loop in my code?

This is the code that i'm using

Code: 
Public Sub testTranspose()    Dim x As Long
    Dim first_row As Long
    Dim last_row As Long
             
    first_row = 3
    last_row = Range("A" & Rows.Count).End(xlUp).Row
     
    For x = first_row To (last_row) Step 13
        Range("A" & x).Resize(13, 2).Copy
        Range("M" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Transpose:=True
    Next
     
End Sub

THANK YOU !!!!!!!

This is my desire output, see picture :

Excel 2007
ABCDEFGLMNOPQRSTUVWXYZ
Please I need to Transpose my data as sample bellow

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00"]MyData[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00FF00"]Heading[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Col-A[/TD]
[TD="align: center"]Col-B[/TD]
[TD="align: center"]Col-C[/TD]
[TD="align: center"]Col-D[/TD]
[TD="align: center"]Col-E[/TD]
[TD="align: center"]Col-F[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #92D050, align: center"]A[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]BB[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-A[/TD]
[TD="bgcolor: #92D050, align: center"]A[/TD]
[TD="bgcolor: #92D050, align: center"]B[/TD]
[TD="bgcolor: #92D050, align: center"]C[/TD]
[TD="bgcolor: #92D050, align: center"]D[/TD]
[TD="bgcolor: #92D050, align: center"]E[/TD]
[TD="bgcolor: #92D050, align: center"]F[/TD]
[TD="bgcolor: #92D050, align: center"]G[/TD]
[TD="bgcolor: #92D050, align: center"]H[/TD]
[TD="bgcolor: #92D050, align: center"]I[/TD]
[TD="bgcolor: #92D050, align: center"]J[/TD]
[TD="bgcolor: #92D050, align: center"]K[/TD]
[TD="bgcolor: #92D050, align: center"]L[/TD]
[TD="bgcolor: #92D050, align: center"]M[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #92D050, align: center"]B[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]CC[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-B[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]3[/TD]
[TD="bgcolor: #00FF00, align: center"]4[/TD]
[TD="bgcolor: #00FF00, align: center"]5[/TD]
[TD="bgcolor: #00FF00, align: center"]6[/TD]
[TD="bgcolor: #00FF00, align: center"]7[/TD]
[TD="bgcolor: #00FF00, align: center"]8[/TD]
[TD="bgcolor: #00FF00, align: center"]9[/TD]
[TD="bgcolor: #00FF00, align: center"]10[/TD]
[TD="bgcolor: #00FF00, align: center"]11[/TD]
[TD="bgcolor: #00FF00, align: center"]12[/TD]
[TD="bgcolor: #00FF00, align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #92D050, align: center"]C[/TD]
[TD="bgcolor: #00FF00, align: center"]3[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]DD[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #92D050, align: center"]D[/TD]
[TD="bgcolor: #00FF00, align: center"]4[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]EE[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-C[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]AA[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #92D050, align: center"]E[/TD]
[TD="bgcolor: #00FF00, align: center"]5[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]FF[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-D[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]26[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #92D050, align: center"]F[/TD]
[TD="bgcolor: #00FF00, align: center"]6[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]GG[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #92D050, align: center"]G[/TD]
[TD="bgcolor: #00FF00, align: center"]7[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-E[/TD]
[TD="align: center"]BB[/TD]
[TD="align: center"]CC[/TD]
[TD="align: center"]DD[/TD]
[TD="align: center"]EE[/TD]
[TD="align: center"]FF[/TD]
[TD="align: center"]GG[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]JJ[/TD]
[TD="align: center"]KK[/TD]
[TD="align: center"]LL[/TD]
[TD="align: center"]MM[/TD]
[TD="align: center"]NN[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #92D050, align: center"]H[/TD]
[TD="bgcolor: #00FF00, align: center"]8[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col-F[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #92D050, align: center"]I[/TD]
[TD="bgcolor: #00FF00, align: center"]9[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]JJ[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #92D050, align: center"]J[/TD]
[TD="bgcolor: #00FF00, align: center"]10[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]KK[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #92D050, align: center"]K[/TD]
[TD="bgcolor: #00FF00, align: center"]11[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]LL[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #92D050, align: center"]L[/TD]
[TD="bgcolor: #00FF00, align: center"]12[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]MM[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #92D050, align: center"]M[/TD]
[TD="bgcolor: #00FF00, align: center"]13[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]NN[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
See if this works for you:
Code: 
Sub testTranspose2()
    Dim first_row As Long, last_row As Long, first_col As Long, last_col As Long, i As Long
    first_row = 3
    first_col = 1
    last_row = Cells(Rows.Count, first_col).End(xlUp).Row
    last_col = Cells(first_row, first_col).End(xlToRight).Column
    Application.ScreenUpdating = False
    For i = 0 To last_col - 1 Step 2
        Range(Cells(first_row, first_col), Cells(last_row, first_col)).Offset(, i).Resize(, 2).Copy
        Range("L" & first_row + 1 + i * 1.5).PasteSpecial Transpose:=True
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Hi Tetra201,


It work perfect!!, I tough that i just need to add an snippet in my code, i was wrong...

Thank you !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Run-Time Error 381
2
Replies
12
Views
201
Micron
M
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
359
Tetra201
Tetra201
F
  • Solved
Transposing filtered items but skip columns
Replies
5
Views
299
Fluff
Fluff
H
  • Solved
Transpose data from columns to rows
Replies
7
Views
583
harzer
H
J
  • Solved
VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another
Replies
5
Views
174
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top