PDA

View Full Version : Excel Auto sort



Peter H
24-01-2004, 08:13 PM
Any way to sort number totals in a column, so the line with the largest number goes to the top. Easy enough manually, and should be a doddle to do this automaticly - except I can't find a way.
Bye

wotz
25-01-2004, 09:07 AM
I have written a macro in excel to to this, and then assigned it to a button at the top of the page. To select the whole range of cells in an ever increasing s/sheet start in the top left cell of the data you want to sort and then use shift/alt/end (I think, from memory) to go to the end of the data.

parry
25-01-2004, 12:43 PM
Presuming you have a header row, it should just be a case of clicking in any cell (with data) within the column you wish to sort then clicking the sort descending button (icon looks like a Z above an A with a down arrow) or selecting Data|Sort from the menu. This will sort with surrounding columns of data moved into the correct position.

If this doesnt work perhaps there is something special about the way you have your data arranged and what you mean by "number totals". In this case it would be easier to see the data to help you so you would need to provide an idea what the data looks like or a link to download the file.

Peter H
25-01-2004, 03:23 PM
Thanks Parry - allready do it that way, but was trying to find a method to do it on the trot. It is for a Golf competetion, and as the scores are entered, to see who is top score. The master sheet is linked to eight more sheets, but have since discovered, that sorting the main sheet auto sorts them all. So life is easy after all.
Again thanks & Bye

parry
25-01-2004, 03:50 PM
Hi again, you can have it sorted on the fly with a macro.

Just advise what column you want sorted and the data range and I will make a macro that will do that for you.

ie I need to know info like sort column B in range A1:D100.

Peter H
25-01-2004, 04:34 PM
Thanks - never used macros yet, but will have to learn sometime. Col H - Cells A4 to 80.
Bye

parry
25-01-2004, 05:18 PM
Hi, this macro will run whenever there is a manual change to a cell in column H in the sheet in question. This means if you manually type the value into the cell then it will automatically resort but not if the cell changes due to a formula.

Therefore, this may be good for sorting your individual sheets but not your master - some changes would need to be done in order for that to be updated. The code presumes you have a header in row 3 with the actual data in rows 4 downards and the last column with data is H.

This code will only run on the sheet where you paste the code so if you have your data arranged the same in each sheet you can repeat the process and copy the code to each sheet. First, just check it works OK.

A thing to note about macros is that the undo button has no effect, so take a copy of your workbook and try this out.

1. Right click a sheet where you want to sort and select View|Code
2. In the right hand side paste the following code then close the visual Basic Editor by selecting File|Close & Return to Excel (or Alt+Q).



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
On Error GoTo ErrHand

'Only run macro if one cell was changed not multiple cells
If Target.Cells.Count > 1 Then GoTo ErrHand

'If change was in col H, then sort col H descending
If Target.Column = 8 Then
Set Rng = Range("A3:H" & Range("H65536").End(xlUp).Row)
Rng.Sort Key1:=Range("H4"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

ErrHand:
Exit Sub

End Sub