Archive

Archive for June, 2011

Quick Transposer


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
Advertisements
Categories: Uncategorized