在应用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