filter for and delete rows in excel using powershell

Question

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.

this may not be do-able for you ... but have you thot about exporting to a CSV and working with that? PoSh can handle CSV files directly, simply, and quickly. [grin] — Lee_Dailey
– Lee_Dailey, Commented Oct 25, 2018 at 18:47
Thanks, I have looked into it, but my CSV knowledge is even worse than my Excel/ powershell knowledge and Ive got 320 lines of code working with powershell to Excel that I cant go back on. I was able to find a usable solution to this question though — T.Marrow
– T.Marrow, Commented Oct 25, 2018 at 20:08
go with what works! [grin] however, if your data is 2d, then CSV format files are FAR easier to work with - and almost always faster. — Lee_Dailey
– Lee_Dailey, Commented Oct 26, 2018 at 4:45

Nas · Accepted Answer · 2018-10-25 20:09:27Z

1

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

answered Oct 25, 2018 at 20:09

Nas

1,2637 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T.Marrow Over a year ago

I was able to find a solution, but yes. This is essentially what the solution is :)

T.Marrow · Accepted Answer · 2018-10-25 20:10:16Z

1

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

answered Oct 25, 2018 at 20:10

T.Marrow

131 silver badge3 bronze badges

Collectives™ on Stack Overflow

filter for and delete rows in excel using powershell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related