PDA

View Full Version : Solved: How to do sorting in a noncontiguous selection



snoopies
10-19-2006, 02:20 AM
Hi all,

I'm not sure how to do the sorting in a noncontiguous selection which has multiple areas.

Example

1
2
4
5
3


1
3
2


1
6
4

There are 3 areas here, and I want to sort them seperately, the results will look like:

1
2
3
4
5


1
2
3


1
4
6

Any quick way to write in VBA? Please advise, thanks a lot!!

mdmackillop
10-19-2006, 02:44 AM
Hi Snoopies.
Try recoding a macro to carry out your sort. You should end up with something like the following (Spaces added for clarity)

Sub Macro2()
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.End(xlDown).Select
Selection.End(xlDown).Select

Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.End(xlDown).Select
Selection.End(xlDown).Select

Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A21"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select
End Sub

Examine the code, find the repetition and the variances and you get


Sub Macro2A()
Range("A1").Select 'Line added
'Add a loop
Do
Range(Selection, Selection.End(xlDown)).Select
'Fix sort key
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.End(xlDown).Select
Selection.End(xlDown).Select
'Find some way to finish
Loop Until ActiveCell.Row = Cells.Rows.Count

Range("A1").Select
End Sub


With a bit of work, this can be improved further, but the basics are there to sort any number of ranges in column A.

BTW if your Excel is prior to 2003, delete this bit from my code wherever it occurs ", _
DataOption1:=xlSortNormal"

snoopies
10-19-2006, 07:12 PM
Wonderful! Thank you so much!

snoopies
10-19-2006, 07:43 PM
Just one more question ...

How can I modify it if I want to sort not only column A, but the entirerow?
Thanks!

Ken Puls
10-19-2006, 11:13 PM
Hi there,

Change the first line of the Do Loop that Malcolm gave you to:

Range(Selection.EntireRow, Selection.End(xlDown).EntireRow).Select

HTH,

snoopies
10-20-2006, 09:21 PM
THANK YOU !!:clap2: