Written by 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
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
Recent Comments