In short, what this script aims to do is take the following data:1AlphaBetaDelta2BetaEchoFoxtrotKilo3 AlphaKilo4 BetaEchoKiloZuluAnd re-arrange like this:1       2        3      4Alpha   Beta     Alpha  BetaBeta    Echo     Kilo   EchoDelta   Foxtrot         Kilo        Kilo            ZuluThe code I have is as follows:      Private Sub PrepareLibrary()Application.ScreenUpdating = FalseDim Rng As RangeDim i As LongDim n As Range Dim x As LongDim Library As WorksheetDim LastRow As LongSet Library = Sheets(Library)i = 1x = 0LastRow = Library.Range(A1).SpecialCells(xlCellTypeLastCell).rowDo While i <= LastRowSet Rng = Library.Range(A & i)Set n = Library.Range(A1)If IsNumeric(Rng) = True Then  x = x + 1 Rng.Copyn.Offset(0, x).Insert Shift:=xlDown i = i + 1ElseIf IsNumeric(Rng) = False ThenRng.Copyn.Offset(1, x).Insert Shift:=xlDowni = i + 1ElseIf Application.WorksheetFunction.CountA(Rng) = 0 Theni = i + 1End IfLoop End SubEssentially, this runs through Column A one at time, copying each cell across individually, pasting into a new row whenever it comes across a number. It works well on small databases, but when expanding out to over 20,000 rows of data, I get a not enough resources message. Willing to attempt an entirely different approach if it seems possible. I'm new to VBA, so that may explain why this code is quite inefficient.
Re-arranging a list into columns
performance;vba;excel
Your naming tells me nothing and doesn't follow Standard VBA naming conventions.Dim i - this is a counter? why not rowNumberDim n - this is also a counter? why not labelNumberDim x - is this a counter? why not nextRowNumberDim Library should be libraryDim LastRow should be lastRowWith library in fact Worksheets have a CodeName property - View Properties window (F4) and the (Name) field can be used as the worksheet name. This way you can avoid Sheets(mySheet) and instead just use mySheet.A good way to speed up your macro is to use Application.Screenupdating = False and Application.Calculation = xlManual and Application.EnableEvents = False. Just be sure to return them to True and xlAutomatic and True before exiting the sub.Additionally, you're doing everything on the sheet, which is slow by nature. Even just reading into an array should be quicker -Option ExplicitPrivate Sub PrepareLibrary()    Application.Screenupdating = False    Dim lastRow As Long    Dim results As Variant    Dim resultsIndex As Long    Dim currentRow As Long    Dim currentColumn As Long    currentColumn = 0    lastRow = Library.Cells(Rows.Count, A).End(xlUp).Row    ReDim results(1 To lastRow)    results = Library.Range(A1:A & lastRow).Value    For resultsIndex = 1 To lastRow        If IsNumeric(results(resultsIndex, 1)) Then            currentColumn = currentColumn + 1            Sheets(Sheet2).Cells(1, currentColumn) = results(resultsIndex, 1)            currentRow = 2        Else:        Sheet2.Cells(currentRow, currentColumn) = results(resultsIndex, 1)        currentRow = currentRow + 1        End If     Next     Application.Screenupdating = TrueEnd SubThat's not a very good example of using the array to the maximum benefit, but it's something.