how to reverse row order

  • Thread starter Thread starter OM
  • Start date Start date
O

OM

Hi,

I am running Excel 2007 and would like find out if it is possible to
reverse the row order for the entire sheet. Each row might have
different number of column.

e.g.

from
1 3 5 2 7
3 5 1 44
4 2 111 7 6 23

to
7 2 5 3 1
44 1 5 3
23 6 7 111 2 4


Thanks
 
Hi,

I am running Excel 2007 and would like find out if it is possible to
reverse the row order for the entire sheet. Each row might have
different number of column.

e.g.

from
1 3 5 2 7
3 5 1 44
4 2 111 7 6 23

to
7 2 5 3 1
44 1 5 3
23 6 7 111 2 4

Thanks
Click to expand...

I don't use xl2007 but this works in xl2003 and has to work in xl2007.
I believe xl2007 has an IFERROR function that would simplify things a
bit.

Assuming the values start in A1 and the row with the largest number of
columns of entries has its rightmost entry in column Z, then in AA1
use the formula...

=IF(ISERROR(OFFSET($Z1,0,COUNT($A1:$Z1)-
COLUMNS($A:Z))),"",OFFSET($Z1,0,COUNT($A1:$Z1)-COLUMNS($A:Z)))

Fill down to the last row of data and across to accommodate the widest
row.

Ken Johnson
 
Noticed no response to this so, decided to brush up some rusty array skills.
It does the job at my end on the sample data you provided.
-Copy the code below (between asterisks) to a module;
-click in a single cell of the area where you want rows reversed;
-run the code.

If you don't know about creating macros see + "Create a macro using
Microsoft Visual Basic" 0n the web page that follows
http://office.microsoft.com/en-us/excel/HP052047111033.aspx

--
Steve

'*****************************************************************
Option Base 1
Sub ReverseRows()
' click on any cell in the area to reverse before running

Dim r As Integer, c As Integer, i As Integer, x As Integer, s As Integer

Dim arFromSheet
Dim arToSheet

Selection.CurrentRegion.Select

r = Selection.Rows.Count
c = Selection.Columns.Count
On Error Resume Next
ReDim arToSheet(r, c)

'Get values from selection as an arFromSheetay
arFromSheet = Selection.Value


For i = 1 To r
s = 0
For x = 1 To c
If arFromSheet(i, c - x + 1) & "" = "" Then
'allow for blank and empty cells
s = s + 1
Else
arToSheet(i, x - s) = arFromSheet(i, c - x + 1)
End If

Next x
Next i

'Put the arFromSheetay back into the worksheet
Selection.Value = arToSheet

End Sub
'***********************************************************************
 
Back
Top