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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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. ;)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,806
Messages
5,513,514
Members
408,955
Latest member
BodanKaraoke

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top