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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
351
Tetra201
Tetra201
F
  • Solved
Transposing filtered items but skip columns
Replies
5
Views
292
Fluff
Fluff
H
  • Solved
Transpose data from columns to rows
Replies
7
Views
568
harzer
H
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
607
pure vito
P
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
532
Alex Blakenburg
A

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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