update Sort Macro

**summer2010** · 09-02-2013, 09:15 AM

I'd like to update a sort macro (I know knowing about macros) on a spread sheet I've inherited.

The info in cells A3:E60 are sorted by this macro, but I would also like info in cells AA3:AB60 and AD3:AN60 sorted along with A3:A60 when the macro is ran.

Sheets(" MON").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" TUE").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" WED").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" THUR").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" FRI").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" SAT").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets(" SUN").Select
Selection.sort Key1:=Range("E60"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

Can anyone help with this please :-)

**aydeegee** · 09-03-2013, 04:25 PM

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:

Please Login or Register  to view this content.

Let me know if you have any questions

Regards
Alastair

**summer2010** · 09-04-2013, 05:13 AM

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 :-)

**summer2010** · 09-04-2013, 06:18 AM

Hi Alastair

I've had a play around and unfortunately only A3:E60 are being sorted.

I'm not sure if the month end macro has a part in this, although it appears not to be (but i'm a novice!!) the month end macro is set to take out info in cells G3:I60,K3:M60,O3:Q60,S3:U60,W3:Y60. Info in cells F,J, N,R, V 3:60 is taken out when there cells G3:I60,K3:M60,O3:Q60,S3:U60,W3:Y60 are blank due to an IF being set.

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

and so on up to SUN.

Thanks for your help Alastair

**aydeegee** · 09-04-2013, 08:47 AM

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

Please Login or Register  to view this content.

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

**summer2010** · 09-04-2013, 09:08 AM

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 :-)

**aydeegee** · 09-04-2013, 04:12 PM

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

Regards
Alastair

**summer2010** · 09-16-2013, 05:21 AM

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

update Sort Macro

LinkBack

Thread Tools

Rate This Thread

Display

update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Re: update Sort Macro

Thread Information

Users Browsing this Thread

Similar Threads

Stop update formulas when macro starts then update when complete

Excel 2007 : How to automatically sort upon update.

Custom Sort Macro - Update on Data Entry

sort table after data update

Update and Sort

Bookmarks

Bookmarks

Posting Permissions