Sub demo()
a = Sheet1.UsedRange
With Sheet2
.[a:a].ClearContents
For i = 1 To UBound(a)
For j = 1 To UBound(a, 2)
If a(i, j) = "" Then Exit For
r = r + 1
.Cells(r, 1) = a(i, j)
Next
Next
End With
End Sub
Sub demo()
a = Sheet1.UsedRange
With Sheet2
.[a:a].ClearContents
For i = 1 To UBound(a)
For j = 1 To UBound(a, 2)
If a(i, j) = "" Then Exit For
r = r + 1
.Cells(r, 1) = a(i, j)
Next
Next
End With
End Sub
Sub test()
Dim ar, br(1 To 100000), k As Long
With Sheet1
For i = 1 To Sheet1.UsedRange.Rows.Count
ar = Range(.Range("a" & i), .Range("a" & i).End(2))
For j = 1 To UBound(ar, 2)
k = k + 1
br(k) = ar(1, j)
Next
Erase ar
Next
End With
Sheet2.[b1].Resize(k) = Application.Transpose(br)
End Sub