update Sort Macro

Hi summer2010

The sort command is quite clever and tries to work things out for itself. The usual way to sort is to specify the area you want and then sort it. If you do not specify the area, then Excel will sort the area that contains the active cell. In this case the "area" is probably bounded by blank cells.

Now that you want to sort more than one area on a sheet, you must declare an area to sort and then declare another etc.

You do not say what you want to happen with the areas between the sorted data ie F3:Z60 and AB3:AC60 - should they be left unsorted? If so, the you need to amend each part of the macro:

Sheets(" MON").Select
Range("A3:E60").select
 Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers

Range("AA3:AB60").select
 Selection.sort Key1:=Range("AB60"), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers

Range("AD3:AN60").select
 Selection.sort Key1:=Range("AN60"), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers

 Sheets(" TUE").Select
etc

Let me know if you have any questions

Regards
Alastair

Hello Alastair

Thank you for your help on this macro. The information in F3:Z60 is set to be removed by a month end macro leaving all the current information in cells A3:E60, AA3:AB60 and AD3:AN60 needing to be sorted. AC will always be blank.


I'll have a play on this shortly and will let you know how I get on :-)

Hi summer2010

I am not clear about what you want (Don't worry - it comes with age )

Are the 3 ranges that I referred to in #2 to be sorted independently of each other or as 1 block? If they are to be considered as 1 block and sorted on column E then

Sheets(" MON").Select
Range("A3:AN60").select
 Selection.sort Key1:=Range("AN60"), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers

 Sheets(" TUE").Select
etc

should work.

Let me know how you get on (and I am aware how frustrating it can be, trying to work with someone else's work!)

Regards
Alastair

Hi Alastair

haha, if it comes with age, then I'm not sure where I'm going wrong this end!!! They're to be sorted as one block, I'd tried going from AN3:AN60 but not your way. I'll give it a go tomorrow

So very true how frustrating amending someone elses work is!!!

Thanks Alastair :-)

Oops - the "Range("AN60")" should read "Range("E60")", but I expect you spotted that !

Regards
Alastair

Hi Alastair

Sorry for the delay, I've not been about for a while. I've had a play around and spotted the E60 range. not sure why but this still don't want to work. The team have decided not to have the information in now. I feel it's been a waste of time on both our parts trying to sort this out and thank you for your help. :-) :-)