Auto sort of rows of data

[Dave Jobes]

hello again, I am wondering if it is possible to auto sort rows in a score sheet.
I have a golf score sheet which comprises 13 rows. Each row contains 22 cells and the total for each player is calculated in the 23rd cell. Its difficult to explain, ideally I would like to post a snap shot of the table here but I cant work out how to do it.

As each players score gets lower (golf) then that players row needs to auto move to the top of the table. Is this possible in excel 2007 please?

I have been able to sort it manually but I was just wondering if there a way of doing this so it happens automatically as data is entered into a row and consequently the total score of that row changes

thanks

 

[footoo]

I have been able to sort it manually

One way is to record a macro and assign it to a button.
https://www.google.com/search?q=how...e.1.69i57j0.3876j0j8&sourceid=chrome&ie=UTF-8

 

[Dave Jobes]

I have assigned a button now but a little bit unsure about the macro.

For my example imagine there are 3 rows of data
Cells A6, A7 and A8 contain team names, call them :Team A, Team B and Team C
B6, B7 and B8 are not used
C6:X6 contains the data entry cells for the scores for team A. Cell Y6 is the calculated total for Team A
C7:X7 contains the data entry cells for the scores for team B. Cell Y7 is the calculated total for Team B
C8:X8 contains the data entry cells for the scores for team C. Cell Y8 is the calculated total for Team C

As various values are entered into each teams data entry cells the calculated totals in the Y column will change

What I am trying to do is be able to press the button at the end of the entries and for the spread sheet to auto sort the rows into the the order of lowest score to highest score. The data selected for sorting will be Y6:Y8 and it will have to be expanded to include the complete row of each team so for example if team A are leading, team C second and Team b third then it will look like this

Team A C6:X6 Y6
Team C C8:X8 Y8
Team B C7:X7 Y7

Is this possible please?

 

[MickG]

Try this:-
This "Change _Event" code should automatically enable the range of rows to be sorted whenever a value is changed, based on column "Y", and starting row 2.

To load code:- Right click sheet "TAB" select "View Code", Vbwindow appears, Paste code Into VBwindow, close Vbwindow.
To run code change a value in column "A to Y".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 25)
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Application.EnableEvents = False
        Rng.Sort key1:=[Y2], order1:=xlAscending, Header:=xlNo
    Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[footoo]

Turn on the macro recorder
Manually sort the data (Data/Sort)
Turn off the recorder
Assign the macro to the button and click whenever you want to sort the data

 

[Dave Jobes]

Thanks Gents

Got it working now, much appreciated

 

[Dave Jobes]

In this sheet I have created three header column. The sheet comprises columns A to P but I only want to auto sort columns D to P. I cant seem to get it to work, The headers are vertical. How can I auto (or manually) sort columns D to P only without affecting columns A, B and C??