My Excel and VBA Blog: Flip row or column

Wednesday, March 30, 2005

Flip row or column

To flip the given row as shown in this figure, use the following macro

Result row

You can use the same macro for fliping columns too, code will find if it's a row or a column.

Note: This code was modified on 07/26/07 for error checking, and removal of Option Base

Sub flip()

Dim Arr As Variant
Dim myrange As Range
Dim vSplitedArr As Variant
Dim arRetArr() As Variant, lArrBnd As Long, i As Long

On Error GoTo flip_Error

Set myrange = Range(Selection.Address)
Arr = myrange 'store the selected values in an array

'split the selected cells address to an array
vSplitedArr = Split(Selection.Address, "$")

' check if Column names are same
If vSplitedArr(1) = vSplitedArr(3) Then
    lArrBnd = UBound(Arr, 1)
    ReDim arRetArr(lArrBnd, 0)
    For i = 0 To lArrBnd - 1
        'flip the array
        arRetArr(i, 0) = Arr(lArrBnd - i, 1)
    Next i
    Range(Selection.Address) = arRetArr
'check if Row numbers are same
ElseIf Replace(vSplitedArr(2), ":", "") = vSplitedArr(4) Then
    lArrBnd = UBound(Arr, 2)
    ReDim arRetArr(0, lArrBnd)
    For i = 0 To lArrBnd - 1
        'flip the array
        arRetArr(0, i) = Arr(1, lArrBnd - i)
    Next i
    Range(Selection.Address) = arRetArr
Else
    MsgBox "Your selection contains multiple rows or columns." & vbCrLf & _
    "This macro will only work on either one column or one row", vbCritical, "Flip Error"
End If

On Error GoTo 0
SmoothExit_flip:
   Exit Sub

flip_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure flip"
    Resume SmoothExit_flip
End Sub

# posted by Ashutosh : 4:19 PM