Deletes every other row in a Microsoft Excel worksheet.
PowerShell$comments = @'
Script name: Delete-EveryOtherRow.ps1
Created on: Sunday, September 02, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
delete every other row on an Excel worksheet?
#http://support.microsoft.com/kb/213610/en-us
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
$xl = new-object -comobject excel.application
$xl.Visible = $True
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item("Sheet1")
$2d = new-object 'object[,]' 20,1
# Fill an array so we can put the numbers into Excel all at once.
for ($i=0; $i -le 19; $i++) {
$2d[$i,0] = $i + 1
}
$r = $ws.Range("A1:A20")
# Put the array into the sheet so we have something to work with.
$r.Value() = $2d
$y = $false
# Change this to $True if you want to
# delete rows 1, 3, 5, and so on.
$i = 1
$r = $ws.UsedRange
$cnt = $r.rows.Count
# Loop once for every row in the selection.
for ($x=1; $x -le $cnt; $x++) {
if ($y -eq $true) {
# ...delete an entire row of cells.
$a = $r.Cells.Item($i).EntireRow.Delete()
}
Else {
# ...increment $i by one so we can cycle through range.
$i++
}
# If ($y is true, make it false; if $y is false, make it true.)
$y = -not($y)
}
$a = Release-Ref($r)
$a = Release-Ref($ws)
$a = Release-Ref($wb)
$a = Release-Ref($xl)
Verified...
