CODEWITHSUNDEEP
excelpowershellpowershell-3.0
T.Marrow
T.Marrow

Reputation: 13

filter for and delete rows in excel using powershell

I am still very new to powershell and feel like this might be a simple question, but I am stuck on my program trying to filter my excel spreadsheet, and then delete the filtered rows.

ideally I would like to filter for all the rows that do NOT say "NO DATA" in column A starting at Row 3. once I have all of those rows I would like to delete them all. Then bring back all the rows that have "NO DATA"

what I cant figure out is how to write the script so that the filter starts at row 2, and how to filter for all the rows that do NOT say "NO DATA" 

$worksheet.usedrange.autofilter(1, -ne "NO DATA", 3)
$worksheet.usedrange.offset(1,0).specialcells(12).Entirerow.Delete() 
$worksheet.ShowAllData()

when I try what seems logical to me I get 2 errors, one is "missing expression" after the',' before -ne, and the other is "unable to get the Auto filter property of the range class" referring to the (Column#, Text, Row#) portion. 

$worksheet.usedrange.autofilter(1, "NO DATA", 2)
$worksheet.usedrange.offset(1,0).specialcells(12).Entirerow.Delete() 
$worksheet.ShowAllData()

This code works but gives me the wrong data set (I'm left with everything I wanted deleted, and it still filters from row 1 and not row 2.

any help would be great.

Upvotes: 0

Views: 4084

Answers (2)

T.Marrow
T.Marrow

Reputation: 13

was able to find a solution, it was actually just as simple as I expected :/ 

$SubCon_Vs_NOA.range("A2").autofilter(1,"<>NO DATA")
$SubCon_Vs_NOA.range("A3:A$rows").Entirerow.Delete() 
$SubCon_Vs_NOA.ShowAllData()

Upvotes: 1

Nas
Nas

Reputation: 1263

If I understand correctly, you're looking for this:

$StartRow = 3
$RowCnt = $worksheet.UsedRange.Rows.Count
$worksheet.Range("A$($StartRow):A$($RowCnt)").AutoFilter(1,"<>NO DATA")
$worksheet.usedrange.offset($StartRow,0).specialcells(12).Entirerow.Delete() 
$worksheet.ShowAllData()

Upvotes: 1

Related Questions