PowerShell - Delete a Range of Rows from Excel After Locating Starting Row

Question

I have a PowerShell script that searches an Excel file for a specific value ("#N/A", from a failed VLOOKUP) that needs to have all of the rows AFTER and including that row deleted quickly.

What I have done so far:

$sheetRange = $sheetData.UsedRange
$colRange = $sheetData.Range("F1")
[void] $sheetRange.Sort($colRange)
$fileWorking.Save()

$strFinder = "#N/A"
$rngSearch = $sheetData.Range("F1").EntireColumn
$txtFound = $rngSearch.Find($strFinder)
$RowFound = ($txtFound.Row)
for (($i = $RowFound); ($i -le $rowsTrans); $i++)
{
  $currRow = $sheetData.Cells.Item($i).EntireRow
  [void]$currRow.Delete()
}
$fileWorking.Save()

The problem is, there are over 200k rows, and I need to delete the entire range instead of one row at a time. While I have muddled around with the syntax some, I can't quite figure out the proper syntax or methods that accomplish this.

Obviously, the for loop simply needs to go, and I need to set the entire range between $RowFound and $rowsTrans... but how is that done?

Can you construct a concatenated string containing all of the addresses then use that to delete? e.g. ...Range("A2, A4, A10, A19").entireRow.Delete That uses a range object definition much like a union of range objects. — user4039065
– user4039065, Commented Apr 7, 2016 at 20:45
@Jeeped My syntax, when assigning a "group" range was wrong. I figured it out on my own, but your answer is in the same vein as my mistake. I was trying to do ...Range("A"+$RowFound+":A"+$rowTrans) (note the ":"). Would you reform your comment into an answer to correct my syntax as described, and I'll accept? ;) — gravity
– gravity, Commented Apr 7, 2016 at 20:59

user4039065 · Accepted Answer · 2016-04-07 21:12:17Z

3

Construct a concatenated string containing all of the addresses as you run through the Range.Find loop. Use that to define the Range object with the Range.EntireRow property to .Delete.

...Range("A2, A4, A10, A19").EntireRow.Delete

That uses a range object definition much like a Union of range objects.

answered Apr 7, 2016 at 21:12

user4039065

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

1 Comment

gravity Over a year ago

Perfect solution. I should point out I'm doing a sort process early on, so I always know everything AFTER and including the first entry needs to be deleted, but your solution is even better if a sort hasn't been done, or isn't reliable (as in, I need to find possible values, and they may not be in order). Definitely above and beyond what I needed!

user9129112 · Accepted Answer · 2021-05-30 21:36:48Z

2

I know this is super old, but I found an even better way to do this with a one-liner. This article sparked my months long journey to this single line... lol

Measure-Command { $sheet.UsedRange.SpecialCells([Microsoft.Office.Interop.Excel.XlCellType]::xlCellTypeFormulas, 16).EntireRow.Delete() } #finds any row with an #N/A and blows it away

Referenced from here.

The comment says what it does, but it searches the ENTIRE file for any cell with #N/A. I'm sure there is a way to make this even more efficient by narrowing to a single column. Hopefully this helps someone like me that couldn't get the range command to work!

BTW, this lowered my deletion times from 12-15 hours to 30 minutes on a ~30,000 line file.

answered May 30, 2021 at 21:36

user9129112

611 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

PowerShell - Delete a Range of Rows from Excel After Locating Starting Row

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