Flip long list in one column from bottom to the top

[Kurt]

Hello All,

How can I take a list of several thousand rows in Column A, get the last column and just completely flip the list from the bottom to the top?

Again as always thanks for any and all help!

Kurt

 

[GTO]

GTO,

Yes that is correct and the list is very long.

Hi Kurt,

I'm not sure how long, "long" is, but if thousands of rows, you will see a speed improvement by flipping the values into an array and working on them there. See if this is in the direction you want.

In a Standard Module:

Option Explicit
    
Sub Invert()
Dim rng As Range
Dim aryOutput, aryTmp
Dim i As Long, lUBnd As Long
    
    '// I used the worksheet's CodeName, change to suit.                                //
    With Sheet1
        '// Presumes a header row.                                                      //
        Set rng = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
        '// Chunk the vals into two arrays.  Really the only reason we're putting vals  //
        '// in aryOutput at this time is to handle the "middle cell" if the range has an//
        '// odd number of cells.                                                        //
        aryTmp = rng.Value
        aryOutput = aryTmp
        
        '// See how many rows (cells) in our range.                                     //
        lUBnd = UBound(aryTmp, 1)
        
        '// Note that "\" divides but just returns the Integer portion; so if lUbnd is 20//
        '// or 21, lUbnd\2 returns 10 either way.                                       //
        For i = 1 To lUBnd \ 2
            '// Similar to ralajer's fill both ends per loop                            //
            aryOutput(i, 1) = aryTmp(lUBnd + 1 - i, 1)
            aryOutput(lUBnd + 1 - i, 1) = aryTmp(i, 1)
        Next
        
        '// Plunk the inverted vals wherever.                                           //
        rng.Offset(, 2).Value = aryOutput
    End With
        
End Sub