Solved: How to do sorting in a noncontiguous selection

[snoopies]

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]

Hi Snoopies.
Try recoding a macro to carry out your sort. You should end up with something like the following (Spaces added for clarity)
[vba]
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
[/vba]
Examine the code, find the repetition and the variances and you get

[vba]
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

[/vba]
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]

Wonderful! Thank you so much!

[snoopies]

Just one more question ...

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

[Ken Puls]

Hi there,

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

[vba]Range(Selection.EntireRow, Selection.End(xlDown).EntireRow).Select[/vba]

HTH,

[snoopies]

THANK YOU !!