Auto Sort Data

[Mdf51]

Still learning VBA so please bare with me..

Trying to Create a sheet that will sort students grades based on rank and name.

Breif example of what im trying to do:

Student Test 1 Test 2 AVG Rank
Bob 81 89 85 2
Sue 91 95 95 1
Joe 79 71 75 3

I would like the sheet to sort based on rank and name in descending order while keeping the whole row for each student in tact. So in this case Sue would move up to the top with all her test scores and average. I would like to use a button to do this as well.

Here is the code I have been trying

Please Login or Register  to view this content.

No changes are made to the sheet after running this Macro

Thank you so much for your help!
Grades Order.xlsm

[Pauleyb]

Some help and then some comments:
your Keys need to be the entire range (e.g. Key1:=Range("E1:E3"))

- If you get stuck on how something should work, try recording a macro. It is not always the best way to achieve something, but it can help you see the parameters and what excel expects.
- Why put this in a worksheet_change event? Maybe you plan on doing other things within the handler, but, generally, event handlers should be avoided unless truly needed and they may cause some user consternation (i.e. every time a name is added, this will sort, but what if the person also wanted to include grades? Now, they have to find the name in the table and add them). It breaks the flow of data input.
- You could just use a table. Lots of nice features. It auto-expands as you add data to the bottom of the table or to the right. There are easy buttons in each header that you can press to sort or filter each column. It can have a table footer which shows Totals, averages, and other calculations. It will not auto-sort every time you add a name, but it will take care your request to 'use a button to do this as well'. Just select a cell in your table and hit Ctrl-T and choose the full range you want to use (I also recommend you create column headers).