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 flipping 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











About the Author

A co-author of Data Science for Fundraising, an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.