VBA Transposing thousands of lines

[Petar Willhite]

I have been working on this for a few days with no luck so any help would be appreciated. What I am needing is to transpose CC2:CS2 to CA2 then transpose Z2:AP2 to CB2. Then proceed to the next row CC3:CS3 transposed starting at CA19 then Z3:AP3 to CB19. That is where I am hitting a snag getting it to paste the next row right below where the previous row was transposed. Each selection will be 17 entries.

The attached file has the first few done as examples.

Let me know If I wasn't clear or if I can help at all.

Thanks for the help in advance.

 

[Luke M]

Hi Petar,

This macro will transpose everything for you. Thanks for a good, clear example.

Sub QuickTrans()
    Dim lastRow As Long
    Dim i As Long
    Dim destRow As Long
   
    'How many columns of data are we copying?
    Const colCopy As Long = 17
   
    'Where is first paste going?
    destRow = 2
   
    Application.ScreenUpdating = False
    With ActiveSheet
        'Find last row with data
        lastRow = .Cells(.Rows.Count, "CC").End(xlUp).Row
       
        For i = 2 To lastRow
            .Cells(i, "CC").Resize(1, colCopy).Copy
            .Cells(destRow, "CA").PasteSpecial Paste:=xlValues, Transpose:=True
           
            'Increment counter
            destRow = destRow + colCopy
        Next i
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub