Powershell How do I sort two columns in Excel ?

For some reason sorting two columns is much harder than sorting one.

$xlSortOnValues = $xlSortNormal = 0
$xlTopToBottom = $xlSummaryBelow = 1
$xlAscending = 1
$xlDescending = 2

[void]$ws1.Activate()
$last = $ws1.UsedRange.SpecialCells(11).Address($False,$false)
$range1 = $ws1.range("A2:$last" )
$range2 = $ws1.range("D2")
$range3 = $ws1.range("A2")
#two-column sort ---> works
$ws1.sort.sortFields.clear()
[void]$ws1.sort.sortFields.add($range2, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
[void]$ws1.sort.sortFields.add($range3, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
$ws1.sort.setRange($range1)
$ws1.sort.header = $xlNo
$ws1.sort.orientation = $xlTopToBottom
$ws1.sort.apply()
Advertisements

3 thoughts on “Powershell How do I sort two columns in Excel ?

  1. Nice post, but I would like to know how to sort just a range of cells in a worksheet. Say I just want to sort the range G139:G145 in a worksheet that has data in columns A to H and only want that range sorted? Thanks. Ken.

  2. This seems to work:

    $xlSortOnValues = $xlSortNormal = 0
    $xlTopToBottom = $xlSummaryBelow = 1
    
    $xlAscending = 1
    $xlDescending = $xlNo = 2
    
    [void]$ws2.Activate()
    $last = $ws2.UsedRange.SpecialCells(11).Address($False,$false)
    $range1 = $ws2.range("A139:H145" )
    $range2 = $ws2.range("G139:G145")
    $ws2.sort.sortFields.clear()
    [void]$ws2.sort.sortFields.add($range2, $xlSortOnValues, $xlAscending,`
    $xlSortNormal)
    $ws2.sort.setRange($range1)
    $ws2.sort.header = $xlNo
    $ws2.sort.orientation = $xlTopToBottom
    $ws2.sort.apply()
    

    Note: this was tested on Worksheet 2. You will need to edit it to match the WS ypu are working with.

  3. Hello, I see that this was posted quite some time ago, however I just came across it and it has helped me a great deal! However I’m trying to modify this to sort by 3 colors and it almost works however I have no idea how to apply the color order I think its something similar to “.SortOnValue.Color _
    = RGB(255, 0, 0)”
    any ideas? – thanks

    I have modified the code as such:

    $xlTopToBottom = $xlSummaryBelow = 1
    $xlAscending = 1
    $xlDescending = 2
    $xlSortOnCellColor = 1

    [void]$WorkSheet.Activate()
    $last = $WorkSheet.UsedRange.SpecialCells(11).Address($False,$false)
    $range1 = $WorkSheet.range("A3:$last" )
    $range2 = $WorkSheet.range("A2")
    $range3 = $WorkSheet.range("B2")
    $range4 = $WorkSheet.range("C2")
    #three-column sort ---> works
    $WorkSheet.sort.sortFields.clear()
    [void]$WorkSheet.sort.sortFields.add($range2, $xlSortOnCellColor, $xlAscending,`
    $xlSortNormal)
    [void]$WorkSheet.sort.sortFields.add($range3, $xlSortOnCellColor, $xlAscending,`
    $xlSortNormal)
    [void]$WorkSheet.sort.sortFields.add($range4, $xlSortOnCellColor, $xlAscending,`
    $xlSortNormal)
    $WorkSheet.sort.setRange($range1)
    $WorkSheet.sort.header = $xlyes
    $WorkSheet.sort.orientation = $xlTopToBottom
    $WorkSheet.sort.apply()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s