simple auto sort

Debbiesweety

New Member
Joined
May 28, 2002
Messages
2
I need to simply autosort column c highest to lowest when data changes, or when closing, or opening, or whatever is easy. It just needs to put the person with the most points at the top of the list... I used office 2010 ... I have surfered around the boards and Idont see anything simple enough for my simple mind to wrap around. Thanks for your help

Deborah
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Do you need other columns included in the sort (if so, which ones). Does C1 contain a heading?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Provided you're manually entering the data in column C this should do the trick:

<font face=Calibri><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:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C:C")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>                Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.ActiveSheet.Sort<br>                        .SortFields.Add Key:=Range("C:C"), _<br>                            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal<br>                            .SetRange Range("C:C")<br>                            .Header = xlGuess<br>                            .MatchCase = <SPAN style="color:#00007F">False</SPAN><br>                            .Orientation = xlTopToBottom<br>                            .SortMethod = xlPinYin<br>                            .Apply<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Noting Peter's comment about the range, you may need to adjust the code accordingly to expand the range to other columns.

To use it right-click on the worksheet tab and select View Code, then paste the above in the new window that opens on the right. ALT+Q will exit you back to Excel to test.

HTH,
 

LunnMB

New Member
Joined
May 25, 2010
Messages
2
I am hoping to do the same sort of thing here except sort by text values...could I use this code for that, assuming excel will determine which text values are greater then the other? ie. "current" > "expired"?

When setting the range, do I need to include the whole spreadsheet, ie. A2 - J142? Should this be included in the code as this?

And to sort the "I" column i modify as so?

' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A2:J142")
Set rng = Target.Parent.Range("I:I")

...and my last question stems from this..

Action if Condition(s) are met (do your thing here...)

what can i put in there to make it seperate the "current" from "expired"??

If this makes any sense I appreciate the help. Thanks.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
As for the first part:

And to sort the "I" column i modify as so?

' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A2:J142")
Set rng = Target.Parent.Range("I:I")

Yes, that should work.

As to the second:

what can i put in there to make it seperate the "current" from "expired"??

You'd have to define what you want to do if those are found and where. ;)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,424
Messages
5,831,549
Members
430,074
Latest member
Francis101

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top