A post from a Pistonhead member in need :
Hopefully someone can help!
I have a list of approx 50000 numbers in ‘Column A’ of spreadsheet. I need to split this list into batches of 1000, populated into Column B onwards.
Anyone with some VBA skill got any ideas? Thanks in advance!
My reply :
Sub QuickTranspose()
Dim rng As Range
Dim StartRowNum As Long
Dim EndRowNum As Long
Dim ColumnOffsetCounter As Long
Dim GroupSize As Long
Dim v
Dim i As Long
Dim TotalRows As Long
GroupSize = 1000
TotalRows = 50000
'Dirty but without range names etc you just need to get the
'selected cell to be the first one in the list
Set rng = Application.Selection
StartRowNum = rng.Rows.Row
For i = 0 To TotalRows / GroupSize
EndRowNum = StartRowNum + GroupSize - 1
v = Range("A" & StartRowNum & ":A" & EndRowNum).Value
Range(rng.Offset(0, i + 1), rng.Offset(GroupSize - 1, i + 1)).Value = v
StartRowNum = EndRowNum + 1
Next i
End Sub
