Sort

[jackdiamond]

Hi,

If I have a table of names and numbers, how can I sort it so that the person with the highest number is always at the top?

I know I can do this by going into sort but how can I get it to change automatically when new data has bee entered?

The number entry is column P and the Names column B

Hope some excel wizard can help!!

Thanks
Stu

 

[Peter_SSs]

Stu

Try this.

1. Right click the sheet name tab and choose "View Code".
2. Copy and paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window.
4. Try data entry

My assumptions are:
- row 1 contains headers.
- the table only goes from column B to column P

If those assumptions are incorrect and you cannot modify the code yourself, post back with more details.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("P")) <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Intersect(ActiveSheet.UsedRange, Columns("B:P"))<br>            .Sort Key1:=Range("P2"), Order1:=xlDescending, Header:=xlYes<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Peter_SSs]

Code above was meant to be:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("P")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Intersect(ActiveSheet.UsedRange, Columns("B:P"))<br>            .Sort Key1:=Range("P2"), Order1:=xlDescending, Header:=xlYes<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>