Prev: How to redistribute microsoft.office.interop.excel?
Next: How to map a shortcut key to a routine in COM AddIn for Excel 2007
From: sort on 11 May 2010 20:55
I want to do two sorts one on column "B" and then by column "E" for a
specific range.

Below is the sort for the first sort. How do I do a nested sort along whith
the below sort?

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _
("B6:B100"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A6:E100")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
From: ozgrid.com on 11 May 2010 23:23
Assumes both Columns have headings

Sub SortAE()
Application.DisplayAlerts = False
'CODENAME
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
With Sheet1
.Range("B1", Cells(.Rows.Count, "B") _
.End(xlUp)).Sort .Range("B1"), xlAscending, , , , , , xlYes
.Range("E1", Cells(.Rows.Count, "E") _
.End(xlUp)).Sort .Range("E1"), xlAscending, , , , , , xlYes
End With
Application.DisplayAlerts = True
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"sort" <sort(a)discussions.microsoft.com> wrote in message
news:D842214C-CCF4-4F3F-8141-9317B1271D2D(a)microsoft.com...
>I want to do two sorts one on column "B" and then by column "E" for a
> specific range.
>
> Below is the sort for the first sort. How do I do a nested sort along
> whith
> the below sort?
>
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _
> ("B6:B100"), SortOn:=xlSortOnValues, Order:=xlAscending,
> DataOption:= _
> xlSortTextAsNumbers
> With ActiveWorkbook.Worksheets("Sheet1").Sort
> .SetRange Range("A6:E100")
> .Header = xlGuess
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
> End With

From: FSt1 on 12 May 2010 00:16
hi
if you record a sort macro, it look something like this.

Range("A6:E100").Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

the recorder can be your friend.
besides, in the code you posted i didn't see any declared sort keys. it
looks like you set the first sort key to the range to sort. odd. does that
code accually work??

Regards
FSt1

"sort" wrote:

> I want to do two sorts one on column "B" and then by column "E" for a
> specific range.
>
> Below is the sort for the first sort. How do I do a nested sort along whith
> the below sort?
>
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _
> ("B6:B100"), SortOn:=xlSortOnValues, Order:=xlAscending,
> DataOption:= _
> xlSortTextAsNumbers
> With ActiveWorkbook.Worksheets("Sheet1").Sort
> .SetRange Range("A6:E100")
> .Header = xlGuess
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
> End With
 | 
Pages: 1
Prev: How to redistribute microsoft.office.interop.excel?
Next: How to map a shortcut key to a routine in COM AddIn for Excel 2007