Transposing data

[Paul_Hossler]

Personally I think manually entering the range to reverse is very error prone and that interacting with the worksheets is lacking performance

I'd use the Selection and arrays

Down side is that only Values get reversed. If you wanted the formats, it'd be a (very) little bit tricker and not as fast

Option Explicit


Sub RevCols()
    Dim aryIn As Variant
    Dim aryTemp() As Variant
    Dim r As Long, c As Long, o As Long
    
    If Not TypeOf Selection Is Range Then Exit Sub
    
    aryIn = Intersect(Selection.Parent.UsedRange, Selection.EntireColumn).Value


    Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Clear


    ReDim aryTemp(LBound(aryIn, 1) To UBound(aryIn, 1), LBound(aryIn, 2) To UBound(aryIn, 2))


    For c = LBound(aryIn, 2) To UBound(aryIn, 2)
        o = LBound(aryIn, 1)
        For r = UBound(aryIn, 1) To LBound(aryIn, 1) Step -1
            aryTemp(o, c) = aryIn(r, c)
            o = o + 1
        Next r
    Next c


    Worksheets("Sheet2").Cells(1, 1).Resize(UBound(aryIn, 1), UBound(aryIn, 2)).Value = aryTemp
End Sub