Reverse order of rows

[Roger Morris]

I want to import .csv files into Excel (that works fine) and then
reverse the order of all rows.

ie if 100 lines were imported I want line 100 to be in row 1 and line 1
to be in row 100.

There isn't a convenient field to sort on

Is there, within Excel, a better way than

a) adding a column of numbers to use in a sort
or
b) having to reverse the line order in the text file before import

At the moment I'm using (b) - with a script I find it easier than (a)

 

[J.E. McGimpsey]

I want to import .csv files into Excel (that works fine) and then
reverse the order of all rows.

ie if 100 lines were imported I want line 100 to be in row 1 and line 1
to be in row 100.

There isn't a convenient field to sort on

Is there, within Excel, a better way than

a) adding a column of numbers to use in a sort
or
b) having to reverse the line order in the text file before import

At the moment I'm using (b) - with a script I find it easier than (a)

You could make (a) into a macro:

Public Sub ReverseRows()
Dim oldCalc As Long
With Application
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlManual
End With
With ActiveSheet.UsedRange
With .Offset(0, .Columns.Count).Resize(, 1)
.Formula = "=ROW()"
Cells(1, 1).CurrentRegion.Sort _
Key1:=.Cells(1, 1), _
Order1:=xlDescending, _
Header:=xlNo
.ClearContents
End With
End With
With Application
.ScreenUpdating = False
.Calculation = oldCalc
End With
End Sub


you could then attach that macro to a toolbar button, menu item or
keyboard shortcut.

 

[Roger Morris]

You could make (a) into a macro:

Public Sub ReverseRows()
Dim oldCalc As Long
With Application
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlManual
End With
With ActiveSheet.UsedRange
With .Offset(0, .Columns.Count).Resize(, 1)
.Formula = "=ROW()"
Cells(1, 1).CurrentRegion.Sort _
Key1:=.Cells(1, 1), _
Order1:=xlDescending, _
Header:=xlNo
.ClearContents
End With
End With
With Application
.ScreenUpdating = False
.Calculation = oldCalc
End With
End Sub


you could then attach that macro to a toolbar button, menu item or
keyboard shortcut.

I added a toolbar button and attached that macro.
It is exactly what is required - works like magic.
Thanks very much. (Now I shall have to learn more about macros)