Submitted By: Kent Finkle

Deletes every other row in a Microsoft Excel worksheet.

PowerShell
Edit|Remove
$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)