Search notes:
Excel Range object: sort
option explicit
dim curDataRow as long
public rngTestData as range
sub main() ' {
fillTestData
'
' Sort on second column: -------+
' |
' V
rngTestData.sort key1 := rngTestData.offset(0,1).resize(1,1), order1 := xlAscending, header := xlNo
end sub ' }
sub fillTestData() ' {
curDataRow = 3
addDataRow 1, "one"
addDataRow 2, "two"
addDataRow 3, "three"
addDataRow 4, "four"
addDataRow 5, "five"
addDataRow 6, "six"
addDataRow 7, "seven"
addDataRow 8, "eight"
addDataRow 9, "nine"
addDataRow 10, "ten"
set rngTestData = range(cells(3,3), cells(curDataRow-1, 4))
end sub ' }
sub addDataRow(numVal as long, numText as string) ' {
cells(curDataRow, 3) = numVal
cells(curDataRow, 4) = numText
curDataRow = curDataRow + 1
end sub ' }
Sorting the selected region in the Immediate Window
Assuming the selection has a header row that contains column names, the selected region can be sorted based on the values of the columns
amount
in the
immediate window like so:
selection.sort header := xlYes, key1 := "amount", order1 := xlAscending
Sort the selection in column J
:
selection.sort header := xlYes, key1 := columns("J"), order1 := xlAscending
Sort the 3rd column of the selection:
selection.sort header := xlYes, key1 := selection.cells(1,3), order1 := xlAscending