Results 1 to 7 of 7

Thread: Excel Auto sort

  1. #1
    Peter H

    Default Excel Auto sort

    Any way to get Excel to Auto sort columns? Doesn't appear in the help file.

  2. #2

    Default Re: Excel Auto sort

    What exactly are you trying to do Peter?

    Are you trying to sort a column of numbers into ascending or descending order?

  3. #3
    Peter H

    Default Re: Excel Auto sort

    Thanks for the interest. It is a entry sheet - split into 4 groups for results.
    The entry sheet has scores entered, and this is linked to the result sheets. At the end, I manualy sort using the sort fuction. What I would like, is as results are entered, the function be automatic, so at a glance you can see the top score on each sheet. At the moment, you have to wait for all scores to come in and then sort. Hope this makes sense.

  4. #4

    Default Re: Excel Auto sort

    Hi Peter, there is no Auto sort function that I am aware of but it's certainly possible to do this with a dynamic macro. If you want I can write one for you but I would need to know what the columns are in the whole range and the order of sort (eg data in cols a-h, with ascending sort on col d).


  5. #5
    Peter H

    Default Re: Excel Auto sort

    Thanks Parry. Data is approx 120 rows - the contest is held over 2 days with day 1 results in col F, day 2 col G, and totals in col H. This master sheet is linked to 4 sheets for handicap groups, and 3 sheets for age groups. Only sort needed is Column G in all 7 sheets - not the master, as I need to keep this order. Sort will be descending by numbers, the highest would be about 90.

  6. #6

    Default Re: Excel Auto sort

    Hi Peter, see the macro below. I have made the assumption you have a header row in row 1, so the code will sort all rows in columns f:h from row 2 downwards. The macro is linked to the change event meaning any change in any cell will run the macro. However, it will only sort after someone changes a cell in row H.

    I suggest you do a test first by copying the workbook and running the code on that as macros cannot be "undone" - the undo button wont do anything.

    To copy the code do the following:-
    1) Open the VB Editor (Alt-F11)
    2) You will see its split into 2 windows. In the left hand side under the folder Microsoft Excel Objects you will see each of your sheets listed. Double click on the sheet.
    3) In the right hand side paste the code (starting from line Private Sub... until End Sub).

    You will need to repeat steps 2-3 for each sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Check that cell changed was in col H. If not, exit.
    If Target.Column <> 8 Then Exit Sub

    'Sort cells f:h by col g descending. Presumes header in row 1
    Range("F:H").Sort Key1:=Range("g1"), Order1:=xlDescending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    End Sub

  7. #7
    Peter H

    Default Re: Excel Auto sort

    Thanks Parry - will give it a go, but will be tomorrow. Weather doesn't look too good for Golf !!

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: 25-01-2004, 04:18 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