# reversing the order of 2 columns

#### lezawang

##### Well-known Member
Hi
Is there any way/trick to reverse a table without using sorting? Here is my table

From

 average grade 0 f 50 c 60 b 80 a

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

To

 average grade 80 a 60 b 50 c 0 f

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Thank you very much.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Peter_SSs

##### MrExcel MVP, Moderator
One way, copied across and down

Excel Workbook
ABCDE
20f80a
350c60b
460b50c
580a0f
Reverse

##### Well-known Member
VBA?
Code:
``````Sub revers()
Dim a As Variant
Dim i, j, lr, t As Integer
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
a = Cells(2, 1).Resize(lr, 2)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
t = 1
For i = lr To 1 Step -1
For j = 1 To 2
b(t, j) = a(i, j)
Next
t = t + 1
Next
Cells(2, 3).Resize(lr, 2) = b
End Sub``````

Last edited:

#### James006

##### Well-known Member
Why is Sorting prohibited ...???

#### lezawang

##### Well-known Member

Thanks all for the help. Not prohibited but I thought there is another way I do not know off. Thanks all once again
Why is Sorting prohibited ...???

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks all for the help. Not prohibited but I thought there is another way I do not know off. Thanks all once again
You're welcome.

.. and of course if the data started out in a jumbled order then sorting wouldn't reverse the table anyway.

Last edited:

##### Well-known Member
You are very well come
Be happy

#### Peter_SSs

##### MrExcel MVP, Moderator
If you are interested, here is a vba way to reverse a table (but keep the headings) without looping through the rows and columns.

Rich (BB code):
``````Sub Reverse_Table()
Dim rws As Long, cols As Long
Dim vRws As Variant, vCols As Variant

With Range("A1").CurrentRegion
rws = .Rows.Count
cols = .Columns.Count
vRws = Application.Transpose(Split("1 " & Join(Application.Transpose(Evaluate(rws + 1 & "- row(1:" & rws - 1 & ")")))))
vCols = Application.Transpose(Evaluate("row(1:" & cols & ")"))
.Offset(, cols + 1).Value = Application.Index(.Value, vRws, vCols)
End With
End Sub``````

Replies
2
Views
229
Replies
3
Views
248
Replies
7
Views
338
Replies
14
Views
423
Replies
1
Views
93