PDA

View Full Version : [SOLVED] VBA Sorting multiple rows



Pianoman14
07-20-2014, 12:43 PM
I am trying to produce a code that will sort the contents of 300 rows of data. The data is in 9 columns and I need it to sort each row into descending order (see example below).

I am sure that there is an easy solution, but as I am quite new to VBA I would really appreciate someone pointing me in the right direction!

Many thanks


2
5
6

3

2
1



6
2

2
7
8
8
2
1


Results
would
be;








6
5
3
2
2
1





8
8
7
6
2
2
2
1

mancubus
07-20-2014, 01:35 PM
Sub Sort_Rows_Desc_Independently()

Dim LastRow As Long, i As Long, calc As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With

With Worksheets("SheetNameHere") 'change to suit
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 1 To LastRow
.Rows(i).Sort Key1:=.Cells(i, 1), Order1:=xlDescending, Header:=xlGuess, Orientation:=xlLeftToRight
Next i
End With

With Application
.EnableEvents = True
.Calculation = calc
End With

End Sub

Pianoman14
07-20-2014, 02:00 PM
Thanks Mancubus,

That's fab, but how could I restrict the sort to just those 9 columns, say columns A to I, leaving the rest of the data in the worksheet alone?

mancubus
07-20-2014, 02:30 PM
you are welcome...


.Range("A" & i & ":I" & i).Sort Key1:=.Cells(i, 1), Order1:=xlDescending, Header:=xlGuess, Orientation:=xlLeftToRight

Pianoman14
07-20-2014, 02:37 PM
Fabulous. Thanks Mancubus