[rama4672]

Can Some one help me here. What I want to be able to do is sort the sheet in time order, So that if a name is added to the bottom of the sheet with a different time than the last entry it will sort it into order.
I have tried 2 codes with varying results on each
The first code selects all the ones with data in them but col d is left blank so it will only select a:c and my times i need sorting are in f.

[vb]
Range("a9").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("f9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
[/vb]

the next one i tried works partly but i have data at offset below the main list that it also moves, I also need it to be able to Keep any information that is on the same row together once it has been sorted.
If I change the second sort range to g9 it works OK but it leaves any information the is associated to the rows where it is

[vb]
Range(Range("a9"), Range("t9").End(xlDown)).Sort Key1:=Range("F9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
[/vb]
TIA

Ian

 

[Anne Troy]

1. How are you entering the time (exactly).
2. What does the formula bar say after a time has been entered (exactly).
3. Why do you need VBA?

 

[rama4672]

OK I probably did't explain it very well, But it starts off as a weekly rota and then i run the macros which then puts all the drivers names in each day with his start and finish time, Then it puts the yardmen,shunter,office at the bottom of the drivers list leaving a space of 7 rows, then it puts the people who are on sick or holiday in another col which is offset by 7 rows and 9 cols.

Now when we are using the daily rota we are using agency that we slot in at what ever time we can get them, What i was looking for was if we added all the agency at the bottom of the list of drivers with their respective start time then hit the sort macro it would sort them. It would be a lot quicker than inserting rows between the times and slotting the agency drivers in,as when we book them we might have 20 agency to book in at once.
I hope this is a bit clearer.

TIA

Ian

 

[Anne Troy]

Can you answer 1 and 2, Ian?

 

[rama4672]

1: the time is entered as the standard time format ie: start time 06:00 finish time 16:45
2: 06:00:00 Is what is displayed in the formula bar

BTW The time is sorted after the macro has run but I need it to be able to do it after data has been added to the sheet

the second vba code above in the first post is what is run after everything else

Ian

 

[XL Guru]

>> but col d is left blank so it will only select
>> a:c and my times i need sorting are in f.

Range("A9").CurrentRegion.Select
Selection.Resize(Selection.Rows.Count, _
Selection.Columns.Count + 3).Select

Rgds,
Andy

 

[rama4672]

thank you for that Andy
I Think that the project i have been working on is now finished, apart from a bit i tweaking in the code that is, But they won't let me rest as they have just given me another one to do, not as hard though (I Hope).
Thanks for all the help that i have received from the people on this board to help me complete this job.