# Auto sort of rows of data

#### Dave Jobes

##### New Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

Last edited:
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".
Code:
``````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
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

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

Thanks Gents

Got it working now, much appreciated

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??

Replies
2
Views
719
Replies
3
Views
342
Replies
1
Views
278
Replies
12
Views
625
Replies
3
Views
270

1,196,367
Messages
6,014,848
Members
441,850
Latest member
peh16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back