Sorting a range in Excel

Getting Started
1

Does anyone have sample code of sorting a range by 3 columns in Excel, all ascending order. What I’m trying doesn’t seem to work and I can’t find any examples online of someone doing this in Xojo.

myWorkSheet.Range("A1:H"+str(numrecords+1,"#")).Sort(myworksheet.range("E1"), office.xlAscending, _ myworksheet.range("A1"), office.xlAscending, _ myworksheet.range("G1"), office.xlAscending, _ office.xlYes)

2

It works fine when I’m only sorting by two columns, but doesn’t work when there’s a third.

3

Hi Daniel,

If all three columns are side-by-side(A1:C7), then this VBA code works:

Range("A1:C7").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:C7") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

This code needs to be converted to Xojo.

An error will occur if multiple selections are to be sorted. An example of multiple selections are row A, row C, and Row E.

[quote]The Command you chose cannot be performed with multiple selections.
Select a single range and click the command again[/quote]

4

That wont work at all. My version does not support the Worksheet.Sort object. It only supports the range.sort method.

5

Hi Daniel,

Which version is on the computer?

Here is a quick example that works on Windows 8.1, in Office 2013.

[code] Dim xl as new ExcelApplication
xl.Workbooks.Add
xl.Visible = True
Dim r as new Random

Dim i as Integer
'Create 3 columns of random data
For i = 1 to 7
xl.Range(“A”+ Cstr(i)).value = CStr(r.InRange(0,100))
xl.Range(“B”+ Cstr(i)).value = CStr(r.InRange(0,100))
xl.Range(“C”+ Cstr(i)).value = CStr(r.InRange(0,100))
Next i

MsgBox “Random numbers were created. Sorting will start”
'Sort
xl.Worksheets(“sheet1”).Range(“A1:C7”).sort(xl.Range(“A1:C7”))

'Show any errors
exception err as NilObjectException
MsgBox err.Message
[/code]

Does this work with your version?

6

I figured it out. It required using range.invoke(“Sort”, paramters) instead of range.sort.

Thank you though.