how to loop macros for the all rows ?

veer-india

New Member
Joined
Jul 30, 2013
Messages
10
i have all the data under A
i want move
a1 to a1
a2 to b1
a3 to c1
a4 to d1
a5 to e1
a6 to f1

and then again
a7 to a2
a8 to b2
and so on....
and the file is too large to do manually
please write a macro for me....
i dont know much about excel.... please give me the code.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
we could write the code for you, but then if criteria change, or it's not quite right, then you will need us to change the code again to suit. It would be far better for you to learn VBA so you can be self sufficient with the codes. The more you use something the better you get at it. If you're unsure about how to use excel, or VBA, there are plenty of books available on this site that can help you get started.

What you're looking for is 2 "for" statements that increment. 1 goes to the bottom of the page (search this site on how to find the last row) and one goes to 6
Then using the cells function in VBA will be able to arrange the code as you need.

If you need help with the code, please post what you have created, and I will be only too happy to assist.
 
Upvote 0
Hi Dermie
Do you really need 2 For statements...You could use one and make the data go into Col "B" and then just delete Col "A"
 
Upvote 0
No code required.

Insert new row A. Select range (A2:A7), which is the same range that you use above. Copy. Move your Activecell to A1. Right Mouse Button, Pastespecial, check box "Transpose" and press OK.
 
Upvote 0
Geez, the Aussies are playing it tough today.....who's gonna break first !!
Maybe not for a one off Teeroy, but the OP did say it was a large file !!
 
Upvote 0
Hi Dermie
Do you really need 2 For statements...You could use one and make the data go into Col "B" and then just delete Col "A"
Very true. A much simpler way of looking at it too. I got stuck going through the steps, and not looking at the whole process.
 
Upvote 0
Welcome to the Mr. Excel Message Board!

The only problem I ran into is the paste, I had to paste in B, since you can't Paste over A (unless its the same shape and size, which transpose does not make it so... anyways... this should work for you.

Code:
Sub TheDirtyDeed()
    Dim lngLstRow As Long
    Dim lngRows(1 To 2) As Long
    Dim lngCols As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count / 6
    lngRows(1) = 1
    lngRows(2) = 6
    lngCols = 1
        
        For i = 1 To lngLstRow
            Range(Cells(lngRows(1), lngCols), Cells(lngRows(2), lngCols)).Copy
            Range("B" & i).Select
            Cells(i, "B").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
            
            lngRows(1) = lngRows(1) + 6
            lngRows(2) = lngRows(2) + 6
        Next i
End Sub
 
Upvote 0
Ok, my shot..
Code:
Sub MM1()
Dim r As Long, lr2 As Long
lr2 = Range("B1")
    For r = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 7
        Range("A" & r & ":A" & r + 5).Copy
        Range("B" & lr2 + 1).PasteSpecial Transpose:=True
        lr2 = Cells(Rows.Count, 2).End(xlUp).Row
    Next r
Columns("A:A").Delete
End Sub
 
Upvote 0
We were similar Michael.
Code:
Sub transposetotheright()
Dim x As Long
Dim lr As Long
x = 1
lr = Cells(Rows.Count, "A").End(xlUp).Row
For I = 1 To lr Step 6
    Cells(I, 1).Resize(6, 1).Copy
    Cells(x, 2).Resize(, 6).PasteSpecial Transpose:=True
    x = x + 1
Next I
Columns("A").EntireColumn.Delete
End Sub
 
Upvote 0
That would be interesting to test time over 10,000 rows
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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