Sub a() Dim arr, arr1() Dim i&, j& With Sheets("sheet1") arr = .Range("A1:A" & .[A65536].End(xlUp).Row) Do i = i + 1 If arr(i, 1) <> "" Then j = j + 1 ReDim Preserve arr1(0 To j) arr1(j - 1) = arr(i, 1) Debug.Print arr1(j - 1) End If Loop Until i >= UBound(arr) .Range("C1:C11") = arr1 End With End Sub
Sub a() Dim arr, arr1() Dim i&, j& With Sheets("sheet1") arr = .Range("A1:A" & .[A65536].End(xlUp).Row) Do i = i + 1 If arr(i, 1) <> "" Then j = j + 1 ReDim Preserve arr1(0 To j) arr1(j - 1) = arr(i, 1) Debug.Print arr1(j - 1) End If Loop Until i >= UBound(arr) .Range("C1:C11") = arr1 End With End Sub