simple auto sort

[Debbiesweety]

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

 

[VoG]

Do you need other columns included in the sort (if so, which ones). Does C1 contain a heading?

 

[Smitty]

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]

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]

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.