Transpone Data

[sandy armstrong]

I have data in column A and i would like to copy 9 rows in that column then transpose the data in column I-R and and contunie to the next 9 rows and transpone again until it reaches the last row in column A. Then once the last part is reached delete column A AND B. I tryed to record a macro to do this but it wont loop on its own and keep going it stops after the first one the i have to press short cut key to move to the next one doing it like that could take a really long time

Thanks to any one who can help...

[Guido Geurs]

Put the range in an array and dump the array in the other column at the right row.
This will transpose blocks of 9 rows in column A to column D and delete columns A and B.

Sub Transpose()
Dim ARRTRANS As Variant
Dim ROWidx As Integer
    ROWidx = 1
    Do Until Cells(ROWidx, 1) = ""
        ARRTRANS = Range("A" & ROWidx).Resize(9)
        Range("D" & ROWidx).Resize(9) = ARRTRANS
        ROWidx = ROWidx + 9
    Loop
    Columns("A:B").Select
    Selection.Delete
End Sub

[sandy armstrong]

Thanks Guido for the response I didnt mentions that there is black row after each block... thanks for the code for this. I do i skip the blanck row???

[Guido Geurs]

This will skip the blanco rows (if there are no more than 2 !)

Sub Transpose()
Dim ARRTRANS As Variant
Dim LASTROWidx As Integer
'§ look for last row with data
    LASTROWidx = 1
    Do Until Cells(LASTROWidx + 1, 1) = "" And _
            Cells(LASTROWidx + 2, 1) = "" And _
            Cells(LASTROWidx + 3, 1) = ""
        LASTROWidx = LASTROWidx + 1
    Loop
'§ put data in array
    ARRTRANS = Range("A1").Resize(LASTROWidx)
'§ dump data in new column
    Range("D1").Resize(LASTROWidx) = ARRTRANS
'§ delete 2 first columns
    Columns("A:B").Select
    Selection.Delete
End Sub

[sandy armstrong]

Hey Guido thanks for the reaspone! when i added the code above it worked fot the first set of data but then never did the other ones. after each set of 9 blocks there is a blank row. I appreciate the help alway!!! your the best :-)

[Guido Geurs]

Is it on sheet 3 to do the transpose?
The attached file was open on sheet 3.
If there are more blanco lines than 2 then you have to increase the check for blanco cells. for 5 blanco lines it must be:

Sub Transpose()
Dim ARRTRANS As Variant
Dim LASTROWidx As Integer
'§ look for last row with data
    LASTROWidx = 1
    Do Until Cells(LASTROWidx + 1, 1) = "" And _
            Cells(LASTROWidx + 2, 1) = "" And _
            Cells(LASTROWidx + 3, 1) = "" And _
            Cells(LASTROWidx + 4, 1) = "" And _
            Cells(LASTROWidx + 5, 1) = ""
        LASTROWidx = LASTROWidx + 1
    Loop
'§ put data in array
    ARRTRANS = Range("A1").Resize(LASTROWidx)
'§ dump data in new column
    Range("D1").Resize(LASTROWidx) = ARRTRANS
'§ delete 2 first columns
    Columns("A:B").Select
    Selection.Delete
End Sub

PS:
In this code there is no transpose of blocks of 9 lines but of the complete column.

Must it be in blocks of 9 lines???

[sandy armstrong]

Hello Guido thats for the reaspone... It still is only working for the first set of 9 blocks I am going to work on it more this afternoon. Thanks for your help... ;-)

[Guido Geurs]

it has transposed all the 999 lines for sheet 3.(see attachment)

[sandy armstrong]

Guido... Ahhh what im i doing wrong???? I am not getting it to transpone for me I dont know why??? I looked at you attachment but it is not working for me!! i was i had your coding abilty...

[Guido Geurs]

I have tested the last attachment on Vista and Office2007 and it's working fine for me.
What is going wrong on your Excel file?

[sandy armstrong]

I dont know What is wrong with it only does the first row and thats it. Thanks Guido I am going to work on today and try to firgure it out i will give you any update once i got it thanks Once again :-)