transpose data

  • Thread starter Thread starter Darius
  • Start date Start date
D

Darius

I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
i = 2
Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub
 
Hi

I think this what your want to do:

Sub Transp()
Dim DestRow As Long, r As Long
DestRow = 2
For r = 2 To 3295 Step 9
Range(Cells(r, 2), Cells(r + 8, 2)).Copy _
Destination:=Cells(DestRow, 3)
DestRow = DestRow + 1
Next
End Sub

Regards,
Per
 
Darius said:
I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
i = 2
Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub
Click to expand...


Put this in C2:K367

=INDIRECT("B"&(ROW()-2)*9+(COLUMN()-1))

then copy / paste special / values.
 
thank you from both,

Per Jessen said:
Hi

I think this what your want to do:

Sub Transp()
Dim DestRow As Long, r As Long
DestRow = 2
For r = 2 To 3295 Step 9
Range(Cells(r, 2), Cells(r + 8, 2)).Copy _
Destination:=Cells(DestRow, 3)
DestRow = DestRow + 1
Next
End Sub

Regards,
Per
Click to expand...
 
Back
Top