Results 1 to 7 of 7

Thread: Excel Auto sort

  1. #1
    Peter H

    Default Excel Auto sort

    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.

  2. #2

    Default Re: Excel Auto sort

    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.

  3. #3

    Default Re: Excel Auto sort

    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.

  4. #4
    Peter H

    Default Re: Excel Auto sort

    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

  5. #5

    Default Re: Excel Auto sort

    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 A1100.

  6. #6
    Peter H

    Default Re: Excel Auto sort

    Thanks - never used macros yet, but will have to learn sometime. Col H - Cells A4 to 80.

  7. #7

    Default Re: Excel Auto sort

    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, _
    End If
    Exit Sub
    End Sub

Similar Threads

  1. Replies: 1
    Last Post: 28-09-2008, 11:35 PM
  2. Excel 2000 sort problem
    By jupiter1 in forum PressF1
    Replies: 4
    Last Post: 17-12-2005, 09:20 PM
  3. Works v Excel Sort Records
    By bruciebear in forum PressF1
    Replies: 0
    Last Post: 17-06-2005, 10:24 AM
  4. Excel sort/order problem...
    By Mike in forum PressF1
    Replies: 29
    Last Post: 27-04-2005, 12:15 PM
  5. Excel Auto sort
    By Peter H in forum PressF1
    Replies: 6
    Last Post: 24-09-2003, 07:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts