Writing a Macro to Use the Transpose Fuction Repeatedly

Question

I am trying move rows of values into a column in Excel. The transpose function work well, but will only move one row at a time. I would like to get a macro that will covert 173 rows of data across three columns into one column. Please see example below. Thanks in advance for any help.

Rows 98,058 98,058 98,314 82,362 82,684 83,326 93,410 93,479 93,761

Column 98,058 98,058 98,314 82,362 82,684 83,326 93410 93479 93761

Answer 1

The following will load the data from the CurrentRegion of A1 into an array and paste into one column, beginning in A5.

I'm assuming the data is numerical, contiguous and that this is a one-off, rather than an exercise that might have to be repeated on data sets of differing sizes. If your data is not contiguous, or not bound by empty cells, then you can hard code the range instead.

Private Sub transposeRows()
Dim inputRange As Variant
Dim myArray() As Long
Dim x As Long
Dim testCell As Range

'Get the range of data to copy'
Set inputRange = Range("A1").CurrentRegion

'Resize array to fit'
ReDim myArray(inputRange.Count - 1)

'Fill up array with data'
For Each testCell In inputRange
    myArray(x) = testCell
    x = x + 1
Next testCell

'Fill destination range'
Range("A5:A" & UBound(myArray) + 5) = WorksheetFunction.Transpose(myArray)

End Sub