powershell - How to delete excess rows in Excel -

i'm trying clean data. have seen number of treads topic. i've followed , applied examples seems wrong script.

what wanted delete entire row when no. column null. when run script half of rows null no. column deleted. have run script multiple times before null rows deleted. there limit in deleting rows in powershell or missing something? here's script:

#cleanup $max = $sheet.usedrange.rows.count  ($i = 6; $i -le $max; $i++) {     if ($sheet.cells.item($i, 1).text -eq "") {         $range = $sheet.cells.item($i, 1).entirerow         $range.delete()     } } 

updates: address deleting problem added $i = $i - 1.. have find way stop loop

 ($i = 6; $i -le $row; $i++) {     if ($sheet.cells.item($i, 1).text -eq "") {         $range = $sheet.cells.item($i, 1).entirerow         [void]$range.delete()         $i = $i - 1     }  }  

if you're deleting rows in "forward" for loop you'll skipping row every time delete row. if row n deleted, former row n+1 becomes row n. however, since index automatically incremented, you're going new row n+1 (former row n+2) in next iteration, skipping former row n+1 (new row n).

example:

consider having table (first column row numbers):

  ┌────────── 1 │   ├────────── 2 │ b   ├────────── 3 │ c   ├──────────

and for loop this:

for ($i=1; $i -le 2; $i++) {   $sheet.rows.item($i).delete() } 

in first iteration $i has value 1, points row 1:

  ┏━━━━━━━━━━ 1 ┃ a   ┡━━━━━━━━━━ 2 │ b   ├────────── 3 │ c   ├──────────

by deleting row, subsequent rows moved up, table becomes (at end of first iteration):

  ┌────────── 1 │ b   ├────────── 2 │ c   ├──────────

the second row has become first row. however, when going next iteration, variable $i incremented 2, points row 2 (the former third row):

  ┌────────── 1 │ b           ← skipped row   ┢━━━━━━━━━━ 2 ┃ c   ┡━━━━━━━━━━

you avoid effect going front, starting @ bottom row:

$max = $sheet.usedrange.rows.count ($i = $max; $i -ge 6; $i--) {     if ($sheet.cells.item($i, 1).text -eq '') {         $range = $sheet.rows.item($i).delete()     } } 

beware .usedrange.rows.count not give number of last row. if have empty rows before first used row need add number offset number of rows in used range:

$max = $sheet.usedrange.rows.count + $sheet.usedrange.row - 1