powershell excel删除组行 [英] powershell excel Deleting group of rows

问题描述

我有一个xlsx文件与成千上万条目
我可以在一个第二个过滤器列中仅显示某些信息与$ workbook.AutoFilter（DATA）

此过滤器只需要一秒钟，但删除第一列=DATA永久使用循环的所有行。

有没有办法捕获数组的隐藏行或范围...或任何我可以.DELETE（）

我尝试过这个

  [void] [Reflection.Assembly] :: LoadWithPartialName（'System.Windows.Forms'）
 $ Excel =新对象-Com Excel.Application 
 $ WorkBook = $ Excel.Workbooks.Open（$ filename）
 $ Excel.visible = $ true 
 $ Excel.selection.autofilter（1，DATA）
 $ sheet = $ workbook （$ i = 2; $ i -le $ max; $ i ++）
 {
 
 $ $ $ b $ row = $ sheet.Cells.Item（$ i，1）.EntireRow 
 if（$ row.hidden -eq $ false）
 {
 $ row.Delete（）
} 
} 
  

FIXED ..向后循环$ i-- *
然而，这让我失望了，因为某种原因大约有10％的可见行未删除。如果我运行它两次，它的作用是扩大这将成为一个更大的问题。

在一个完美的世界，我想要这样的东西

  $ Excel.selection.autofilter（1，DATA）。DELETE（）
  

提前感谢天才可能有任何提示或技巧。

更新：谢谢Graimer，你是对的，我必须循环在其他方向，这仍然需要相当一段时间与10,000多个条目...我正在寻找一种方法来做到没有手动循环。

如果我去$ Excel.visible = $ true，然后$ Excel.selection.autofilter（1，DATA）...然后作为一个用户我ctrl + A并删除选定的行..它更快地手动然后循环过程...我不能帮助，但认为必须有一些方法来编写该动作。

解决方案

在应用fiter之后，应该很容易
，从row1到Lastrow中选择一个范围，删除范围。
因为过滤器只显示一个值，范围不能选择隐藏单元格

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")

This filter only takes a second however deleting all rows whos first column = "DATA" takes forever with a loop.

Is there a way to capture an array of the hidden rows or a range... or anything that I could .DELETE()

I tried this

[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()
    }
}

FIXED.. loop backwards $i-- * 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.

In a perfect world I would like something like this

$Excel.selection.autofilter(1,"DATA").DELETE()

Thanks in advance for any hints or tricks you geniuses may have.

Update: 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.

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.

解决方案

Turned out to be pretty easy after applying a fiter select a range from row1 to Lastrow, delete range. Because the filter is only showing that one value the range cannot select hidden cells

这篇关于powershell excel删除组行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！