Excel - Sort Columns

[JohnBailey]

I've been searching all over for the answer. Clearly I do not know what to call what I'm looking for, so I'll do my best to specifically explain.

I want to be able to sort by first Column A then B then C in Ascending order. However, I'm at a loss.

I know $oExcel.Range().Sort does sorting with proper parameters. However, I'm at a loss for how to make it do what I said above.

ANSWER

Thanks Blue_Darche for the help

#426800

Edited November 1, 2007 by JohnBailey

[Blue_Drache]

Per the Microsoft VB help file buried inside of Excel...

Sort Method

See AlsoApplies ToExampleSpecificsSorts a PivotTable report, a range, or the active region if the specified range contains only one cell.

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

expression Required. An expression that returns one of the objects in the Applies To list.

Key1 Optional Variant. The first sort field, as either text (a PivotTable field or range name) or a Range object ("Dept" or Cells(1, 1), for example).

Order1 Optional XlSortOrder. The sort order for the field or range specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.

xlDescending. Sorts Key1 in descending order.

xlAscending default. Sorts Key1 in ascending order.

Key2 Optional Variant. The second sort field, as either text (a PivotTable field or range name) or a Range object. If you omit this argument, theres no second sort field. Cannot be used when sorting Pivot Table reports.

Type Optional Variant. Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports.

XlSortType can be one of these XlSortType constants.

xlSortLabels. Sorts the PivotTable report by labels.

xlSortValues. Sorts the PivotTable report by values.

Order2 Optional XlSortOrder. The sort order for the field or range specified in Key2. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.

xlDescending. Sorts Key2 in descending order.

xlAscending default. Sorts Key2 in ascending order.

Key3 Optional Variant. The third sort field, as either text (a range name) or a Range object. If you omit this argument, theres no third sort field. Cannot be used when sorting PivotTable reports.

Order3 Optional XlSortOrder. The sort order for the field or range specified in Key3. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.

xlDescending. Sorts Key3 in descending order.

xlAscending default. Sorts Key3 in ascending order.

Header Optional XlYesNoGuess. Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.

XlYesNoGuess can be one of these XlYesNoGuess constants.

xlGuess. Let Microsoft Excel determine whether theres a header, and to determine where it is, if there is one.

xlNo default. (The entire range should be sorted).

xlYes. (The entire range should not be sorted).

OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom , a normal sort is used.

MatchCase Optional Variant. True to do a case-sensitive sort; False to do a sort thats not case sensitive. Cannot be used when sorting PivotTable reports.

Orientation Optional XlSortOrientation. The sort orientation.

XlSortOrientation can be one of these XlSortOrientation constants.

xlSortRows default. Sorts by row.

xlSortColumns. Sorts by column.

SortMethod Optional XlSortMethod. The type of sort. Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that youve selected or installed.

XlSortMethod can be one of these XlSortMethod constants.

xlStroke Sorting by the quantity of strokes in each character.

xlPinYin default. Phonetic Chinese sort order for characters.

DataOption1 Optional XlSortDataOption. Specifies how to sort text in key 1. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.

xlSortTextAsNumbers. Treat text as numeric data for the sort.

xlSortNormal default. Sorts numeric and text data separately.

DataOption2 Optional XlSortDataOption. Specifies how to sort text in key 2. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.

xlSortTextAsNumbers. Treats text as numeric data for the sort.

xlSortNormal default. Sorts numeric and text data separately.

DataOption3 Optional XlSortDataOption. Specifies how to sort text in key 3. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.

xlSortTextAsNumbers. Treats text as numeric data for the sort.

xlSortNormal default. Sorts numeric and text data separately.

Remarks

The settings for Header, Order1, Order2, Order3, OrderCustom, and Orientation are saved, for the particular worksheet, each time you use this method. If you dont specify values for these arguments the next time you call the method, the saved values are used. Set these arguments explicitly each time you use Sort method, if you choose not to use the saved values.

Text strings which are not convertible to numeric data are sorted normally.

Note If no arguments are defined with the Sort method, Microsoft Excel will sort the selection, chosen to be sorted, in ascending order.

Example

This example sorts the range A1:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers. This example assumes there is data in the range A1:C20.

Sub SortRange1()

Worksheets("Sheet1").Range("A1:C20").Sort _

Key1:=Worksheets("Sheet1").Range("A1"), _

Key2:=Worksheets("Sheet1").Range("B1")

End Sub

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. The Sort method determines the active region automatically.

Sub SortRange2()

Worksheets("Sheet1").Range("A1").Sort _

Key1:=Worksheets("Sheet1").Columns("A"), _

Header:=xlGuess

End Sub

[JohnBailey]

Per the Microsoft VB help file buried inside of Excel...

haha oh gee I'm embarrassed. That is the simple place to look thank you!

[JohnBailey]

What am I doing wrong?

; Requires: http://www.autoitscript.com/forum/index.php?showtopic=34302
#include <ExcelCOM_UDF.au3>
#include <Date.au3>
#include <Array.au3>
Local $c1[1]; Date
For $_x = 1 to 10
    Local $entry = _DateAdd('D',Random(-2,0,1),_NowCalcDate())
    _ArrayAdd($c1,$entry)
Next
Local $c2[1]; Number
For $_x = 1 to 10
    Local $entry =Random(0,400,1) 
    _ArrayAdd($c2,$entry)
Next
Local $c3[1]; Letters
For $_x = 1 to 10
    Local $_stringToAdd = ''
    For $_y = 1 to Random(1,5,1)
        $_stringToAdd &= Chr(Random(65,90))
    Next
    _ArrayAdd($c3,$_stringToAdd)
Next
    
Local $_oExcel =  _ExcelBookNew(1)
_ExcelWriteArray($_oExcel, 1, 1, $c1,1)
_ExcelWriteArray($_oExcel, 1, 2, $c2,1)
_ExcelWriteArray($_oExcel, 1, 3, $c3,1)
Sleep(3000)
Local $Header = 0
Local $OrderCustom = True
Local $MatchCase = False
Local $Orientation = 2
Local $SortMethod = Default
Local $DataOption1 = Default
;                               Key1,                Order1,  Key2 ,                   Type,  Order2,     Key3,           Order3,  
$_oExcel.Range('A2:C11').Sort ($_oExcel.Columns('A'),     2,  $_oExcel.Columns('B'),Default,  2,           $_oExcel.Columns('C'),2,   $Header, $OrderCustom, $MatchCase, $Orientation, $SortMethod, $DataOption1)

Error:

Excel UDF - testing.au3 (35) : ==> The requested action with this object has failed.: 
$_oExcel.Range('A2:C11').Sort ($_oExcel.WorkSheets("Sheet1").Columns('A'),    2,  $_oExcel.Columns('B'),Default,  2,           $_oExcel.Columns('C'),2,   $Header, $OrderCustom, $MatchCase, $Orientation, $SortMethod, $DataOption1) 
$_oExcel.Range('A2:C11').Sort ($_oExcel.WorkSheets("Sheet1").Columns('A'),    2,  $_oExcel.Columns('B'),Default,  2,           $_oExcel.Columns('C'),2,   $Header, $OrderCustom, $MatchCase, $Orientation, $SortMethod, $DataOption1)^ ERROR

Edited October 31, 2007 by JohnBailey

[Blue_Drache]

You may have to explicitly type the varaible you're sending to the COM function. Don't send a string where it's expecting an integer. Edited October 31, 2007 by Blue_Drache

[JohnBailey]

You may have to explicitly type the varaible you're sending to the COM function. Don't send a string where it's expecting an integer.

Where specifically am I sending a string that may be expecting a integer? Are you referring to Range('A2:C11') or $_oExcel.Columns('A')

[Blue_Drache]

Nevermind that, I chuck rocks.

Did you build a working string in VB first and then try to break it down to AutoIt?

Range("A1:C11").Sort Range("A1"), xlAscending, Range("C1"), , xlAscending, Range("B1"), xlAscending, xlNo, , True, xlSortColumns, , xlSortTextAsNumbers, xlSortTextAsNumbers, xlSortTextAsNumbers

Or ... if you use the macro builder in Excel, you get:

Rows("1:11").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
        xlNo, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

Edit:

Actually I was right the first time around.

the .columns() property accepts integers, not strings, but returns a range.

Columns Property

See AlsoApplies ToExampleSpecificsColumns property as it applies to the Application object.

Returns a Range object that represents all the columns on the active worksheet. If the active document isn't a worksheet, the Columns property fails. Read-only.

expression.Columns

expression Required. An expression that returns an object in the Applies To List.

Columns property as it applies to the Range object.

Returns a Range object that represents the columns in the specified range. Read-only.

expression.Columns

expression Required. An expression that returns an object in the Applies To List.

Columns property as it applies to the WorkSheet object.

Returns a Range object that represents all the columns on the specified worksheet. Read-only.

expression.Columns

expression Required. An expression that returns an object in the Applies To List.

For information about returning a single member of a collection, see Returning an Object from a Collection.

Remarks

Using this property without an object qualifier is equivalent to using ActiveSheet.Columns.

When applied to a Range object that's a multiple-area selection, this property returns columns from only the first area of the range. For example, if the Range object has two areas A1:B2 and C3:D4 Selection.Columns.Count returns 2, not 4. To use this property on a range that may contain a multiple-area selection, test Areas.Count to determine whether the range contains more than one area. If it does, loop over each area in the range.

Example

This example formats the font of column one (column A) on Sheet1 as bold.

Worksheets("Sheet1").Columns(1).Font.Bold = True

This example sets the value of every cell in column one in the range named "myRange" to 0 (zero).

Range("myRange").Columns(1).Value = 0

This example displays the number of columns in the selection on Sheet1. If more than one area is selected, the example loops through each area.

Worksheets("Sheet1").Activate

areaCount = Selection.Areas.Count

If areaCount <= 1 Then

MsgBox "The selection contains " & _

Selection.Columns.Count & " columns."

Else

For i = 1 To areaCount

MsgBox "Area " & i & " of the selection contains " & _

Selection.Areas(i).Columns.Count & " columns."

Next i

End If

Edited October 31, 2007 by Blue_Drache

[JohnBailey]

Nevermind that, I chuck rocks.

Did you build a working string in VB first and then try to break it down to AutoIt?

Range("A1:C11").Sort Range("A1"), xlAscending, Range("C1"), , xlAscending, Range("B1"), xlAscending, xlNo, , True, xlSortColumns, , xlSortTextAsNumbers, xlSortTextAsNumbers, xlSortTextAsNumbers

Or ... if you use the macro builder in Excel, you get:

Rows("1:11").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
        xlNo, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

Edit:

Actually I was right the first time around.

the .columns() property accepts integers, not strings, but returns a range.

Omitting the header ordercustom etc. variables allowed it to work. Not sure why

$_oExcel.Range('A1:C11').Sort ($_oExcel.Range('A1'),2, $_oExcel.Range('B1'),Default, 2, $_oExcel.Range('C1'),2);, $Header, $OrderCustom, $MatchCase, $Orientation, $SortMethod, $DataOption1)

Thanks!

[JohnBailey]

the .columns() property accepts integers, not strings, but returns a range.

Nice!

[Blue_Drache]

Omitting the header ordercustom etc. variables allowed it to work. Not sure why

$_oExcel.Range('A1:C11').Sort ($_oExcel.Range('A1'),2, $_oExcel.Range('B1'),Default, 2, $_oExcel.Range('C1'),2);, $Header, $OrderCustom, $MatchCase, $Orientation, $SortMethod, $DataOption1)

Thanks!

It allows it to work because you're telling it to accept the DEFAULTS, which, though nice, may not be what you want 100% of the time.

[JohnBailey]

How come the following sorts starting with row 1 no matter what?

Local $row = Random(1,4,1)
ConsoleWrite('Row '& $row &@LF)
$_oExcel.Range(String('A'&$row)).Sort ($_oExcel.Range(String('A'&$row)),1, $_oExcel.Range(String('B'&$row)),Default, 1, $_oExcel.Range(String('C'&$row)),1)

[Blue_Drache]

$_oExcel.Range(String('A'&$row)).Sort ($_oExcel.Range(String('A'&$row)),

Because you're telling it to start with row 1

[JohnBailey]

$_oExcel.Range(String('A'&$row)).Sort ($_oExcel.Range(String('A'&$row)),

Because you're telling it to start with row 1

? How? $row is randomly being set.

Edit

Even if $row is set to 3 it still sorts as though row = 1

Edited November 1, 2007 by JohnBailey

[JohnBailey]

This does the trick. It seems (and I don't know why) that you must use a range like A1:C11 to sort starting at a specific row. You can see how you can customize. I just did a basic customize request.

expandcollapse popup

#include <ExcelCOM_UDF.au3>; = http://www.autoitscript.com/forum/index.php?showtopic=34302
#include <Date.au3>
#include <Array.au3>



Local $c1[1]; Date
For $_x = 1 to 10
    Local $entry = _DateAdd('D',Random(-2,0,1),_NowCalcDate())
    _ArrayAdd($c1,$entry)
Next
Local $c2[1]; Number
For $_x = 1 to 10
    Local $entry =Random(0,400,1) 
    _ArrayAdd($c2,$entry)
Next
Local $c3[1]; Letters
For $_x = 1 to 10
    Local $_stringToAdd = ''
    For $_y = 1 to Random(1,5,1)
        $_stringToAdd &= Chr(Random(65,90))
    Next
    _ArrayAdd($c3,$_stringToAdd)
Next
    
Local $_oExcel =  _ExcelBookNew(1)
_ExcelWriteArray($_oExcel, 1, 1, $c1,1)
_ExcelWriteArray($_oExcel, 1, 2, $c2,1)
_ExcelWriteArray($_oExcel, 1, 3, $c3,1)
Sleep(3000)

Local $_lastCellGet = _ExcelSheetUsedRangeGet($_oExcel, 'Sheet1')
Local $_lastCellColFound = StringRegExpReplace($_lastCellGet[0],'[0-9]','')
Local $_lastCellRowFound = $_lastCellGet[3]

Local $_rowStart = InputBox('Excel COM','What row to start the sort:','2');Random(1,4,1)
Local $_lastCellRow = InputBox('Excel COM','What row to end the sort (the max is default):',$_lastCellRowFound);$_lastCellRowFound
Local $_lastCellCol = $_lastCellColFound
Local $_order = 2

Local $_lastCell = $_lastCellCol&$_lastCellRow
$_oExcel.Range(String('A'&$_rowStart&':'&$_lastCell)).Sort ($_oExcel.Range(String('A'&$_rowStart)),$_order, $_oExcel.Range(String('B'&$_rowStart)),Default, $_order, $_oExcel.Range(String('C'&$_rowStart)),$_order)