Vladimir Bundalo Vladimir Bundalo - 3 months ago 133
PowerShell Question

How to exclude first row from sorting out Excel column by using PowerShell

I want to sort out one column (it is a target date and I want to sort it out by earliest date).
The thing is that the first row is a text (name of the column), so when I sort it out by column F (Target Date), text row goes to the very end of the excel document.
I would like for this column names to stay and be on top of every column.

Column I want to sort out

Here is the code:

$objRange = $worksheet.UsedRange
$objRange2 = $Excel.Range("F2")
[void]$objRange.Sort($objRange2.Range("F2"))


I am not sure how to exclude from sorting this first row.

Answer

Since your range has headers, you have to add an argument to the Sort method to indicate it should not sort the headers.

Your range command would then look like this:

[void]$objRange.Sort($objRange2,1,$null,$null,1,$null,1,1)

The Sort method doesn't like $null for the xlSortOrder parameters so I just specified 1 which means ascending. If you need descending, use 2. The last 1 is the parameter we really care about. Specifying 1 means yes, the range has headers that should not be included in the sort operation.

Syntax reference:

https://msdn.microsoft.com/en-us/library/office/ff840646.aspx