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