Databases
StackOverflow2013
Postsdbo
POpowershell excel Deleting group of rows
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POpowershell excel Deleting group of rows
    text
    copied!<p>I have an xlsx file with thousands of entries I can within a second filter a column to show only certain information with $workbook.AutoFilter("DATA")</p> <p>This filter only takes a second however deleting all rows whos first column = "DATA" takes forever with a loop.</p> <p>Is there a way to capture an array of the hidden rows or a range... or anything that I could .DELETE()</p> <p>I tried this</p> <pre><code>[void] [Reflection.Assembly]::LoadWithPartialName( 'System.Windows.Forms' ) $Excel = New-Object -Com Excel.Application $WorkBook = $Excel.Workbooks.Open($filename) $Excel.visible = $true $Excel.selection.autofilter(1,"DATA") $sheet = $workbook.Sheets.Item(1) $max = $sheet.UsedRange.Rows.Count for ($i=2; $i -le $max; $i++) { $row = $sheet.Cells.Item($i,1).EntireRow if ($row.hidden -eq $false) { $row.Delete() } } </code></pre> <p><em>FIXED</em>.. loop backwards $i-- <strong>*</strong> However This failed me misserably because for some reason it leaves roughly 10% of the visabled rows undeleted. If I run it twice it works however scaling up this would become a bigger issue.</p> <p>In a perfect world I would like something like this</p> <pre><code>$Excel.selection.autofilter(1,"DATA").DELETE() </code></pre> <p>Thanks in advance for any hints or tricks you geniuses may have.</p> <p><strong>Update:</strong> Thanks Graimer, you are right I have to loop in the other directions, this still takes quite some time with 10,000+ entries... I am looking for a way to do it without the manual loop.</p> <p>If I go $Excel.visible = $true, and then $Excel.selection.autofilter(1,"DATA")... then as a user I ctrl+A and delete the selected rows... its quicker manually then the looping process... I cant help but think there MUST be some way to script that action.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload