Transpose of Rows into 25 columns

[waqar1239]

Hello Team,

I am having new challenege in the excel. I have a 9000 rows in one column and i want to split the rows into 6 columns.Means first 6 rows convert into 6 colums and then next 6 rows become the net row of the new data example shown below.
Main Data

Output Data File




I apprceif someonecan help me.

Cheers

 

[lrobbo314]

I think this will do what you are looking for. This code assumes that your data is in column A, and the results are being output to columns F:K. You can adjust those lines of code as needed.

Sub Xpose()
Dim AR() As Variant: AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As String

For i = LBound(AR) To UBound(AR)
    tmp = tmp & AR(i, 1) & ","
    If i Mod 6 = 0 Then AL.Add tmp: tmp = vbNullString
Next i

With Range("F1").Resize(AL.Count, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, comma:=True
End With

End Sub