Sort automatically?

RickeyS

Board Regular
Joined
Jun 23, 2006
Messages
222
I have a spreadsheet that keeps track of overtime hrs worked, which based on person with lowest hrs I can assign the next overtime to the person with the lowest hrs. When I put in hours worked I have a column that keeps a running total.
Here's what I need:
Once I enter someone's hours and it auto sums I want the sheet to automatically resort for lowest hours (running total). How would I do this?
Thanks
Rick
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,004
Office Version
  1. 365
  2. 2016
Hi Rick

You could try this in the sheet module for the sheet in question

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True

    Range("C2:C50").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

Change the C2:C50 to your range and C2 to your sort start point

KR

Dave

EDIT: the C2:C50 will need to relate to the entire range you want to sort so for example if your total was in C and your entery points were A and B it would read Range("A2:C50"). Sorry for got to clarify
[/code]
 

RickeyS

Board Regular
Joined
Jun 23, 2006
Messages
222
Okay I got this far and it works, but it will update the sheet if any cell has been changed and I would like it to update only if the following cells have changed (F8:S28 or F34:S50) What do I need to do?

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A8:T28").Select
Selection.Sort Key1:=Range("T8"), Order1:=xlAscending, Key2:=Range("C8") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A34:T50").Select
Selection.Sort Key1:=Range("T34"), Order1:=xlAscending, Key2:=Range("C34" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("F8").Select
End Sub


Thanks
Rick
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How's this (not tested):

<font face=Tahoma><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)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <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>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("F8:S28, F34:S50")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <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>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            Range("A8:T28").Sort Key1:=Range("T8"), Order1:=xlAscending, Key2:=Range("C8") _
                , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                <SPAN style="color:#00007F">False</SPAN>, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                :=xlSortNormal
            Range("A34:T50").Sort Key1:=Range("T34"), Order1:=xlAscending, Key2:=Range("C34" _
                ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                <SPAN style="color:#00007F">False</SPAN>, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                :=xlSortNormal
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

RickeyS

Board Regular
Joined
Jun 23, 2006
Messages
222

ADVERTISEMENT

Thanks Smitty, Works great.

Thanks again,
Rick
 

KWD

New Member
Joined
Jun 8, 2006
Messages
41
Smity,

I am trying to do a very similar thing, only sorting when a change is made to a certain range. Except, I don't make the change to the range to be sorted directly, it is by formula which references another worksheet. (i.e I wish to sort a range in worksheet A. Worksheet A has a formula which references worksheet b, when I make a change in worksheet b thus changing the cells in worksheet a, your code as written does not activate and sort it). Does that make sence?

You code does not seem to "see this change" and thereby "auto sort". Any thought on how to change this to make it work?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

If you want to trigger an event based on a formula calculating, you need to use a sheet calculate evnt, which does not support the Target argument.

I.E.

Code:
Private Sub Worksheet_Calculate()
  '  Do something
End Sub

If you give more details someone can start on an answer.

Smitty
 

KWD

New Member
Joined
Jun 8, 2006
Messages
41
Here's a quick sample of the sheet I am trying to sort (if this is not clear I'll post the sheet).

I want to sort range c5:F21 (sort decending on column f). Here are sample values (these are standings for a football pool)

c5=RANK(F5,$F$5:$F$21)&'Week 1'!$N$1
d5='Pro Playoffs'!$N$22
Column E is empty
f5=IF(LEFT(D5,FIND("-",D5)-1)="0",0,LEFT(D5,FIND("-",D5)-1)/(LEFT(D5,FIND("-",D5)-1)+RIGHT(D5,LEN(D5)-(FIND("-",D5))))) (is a %)

Column c is a players rank and name (looks like "1 Kevin")
Column d is players record (looks like 99-18)
Column f is the players pick percentage

The ramining cells are similar, they reference the same worksheets just different cells. Columns D and F will be the ones that change. Is this enough to go on? I'll want to sort other ranges on this sheet, but if we can get one working I hope I can figure out the rest.

Thanks
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Here's a start:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
    Range("C5:F21").Sort Key1:=Range("C5"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Note that I use Excel 2003, which has expanded sort options from prior versions, so it'll likely bomb in 2000 or earlier. In which case, just record your sort, then append the code.

Smitty
 

KWD

New Member
Joined
Jun 8, 2006
Messages
41
Thanks that does work!

One problem. When I make a change that changes the sort range, excel now appears to be in a endless loop "Calculating cells". It gets up to about 80%, then starts again at 0%. The sort completes, but maybe there is some cyclic reference?

Thanks for the continuing help
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

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