All cells in one column in order

[zeroist]

Hi,
I need to put all cells in A column in order.first copy first row in A1,then second,third rows... in A column .if A column exceeded 1,000,000 rows continue to b column .
in sample page 1 is original,page 2 is what is needed.in order.xlsx
cells have both text and numbers.(not only numbers)

[alansidman]

What is the maximum number of cells that will have to be transposed. You indicated that if it exceeds 1MM that you need to have data continue on to Column B. Would it possibly exceed 2MM?

[zeroist]

it has 200,000 rows and 19 columns. so 200,000*19=3,800,000 cells has to be ordered in column in A and B even in C.

[zeroist]

What does it make hard? I can divide 200,000 rows into two part.So just column A and B is enough for 1,900,000 cells.

[nilem]

Hi zeroist,
maybe so

Sub ertert()
Dim x, y(), i&, j&, k&, n&
Application.ScreenUpdating = False
x = Sheets("Sayfa1").Range("A1").CurrentRegion.Value
Const ch As Long = 1000000: ReDim y(1 To ch, 1 To 1)
For i = 1 To UBound(x)
    For j = 1 To UBound(x, 2)
        k = k + 1
        If k > ch Then
            n = n + 1
            Sheets("Sayfa2").Cells(1, n).Resize(ch).Value = y()
            k = 1
        End If
        y(k, 1) = x(i, j)
    Next j
Next i
If k < ch Then Sheets("Sayfa2").Cells(1, n + 1).Resize(k).Value = y()
Application.ScreenUpdating = True
End Sub

[zeroist]

Thank you so much.it seems working.