Powershell to loop through excel sheet and look for "" cell - delete entire row

Question

_{Wednesday, May 30, 2018 11:16 AM}

.....

All replies (19)

_{Wednesday, May 30, 2018 2:22 PM}

As always this requires looping backwards.  When you delete a row the original range is rearranged.  Looping backwards prevents this.

\(ツ)_/

_{Wednesday, May 30, 2018 2:52 PM}

How to count backwards in PowerSHell:

99..1

$count = $wb.Worksheets[1].UsedRange.Columns[10].Cells.Count
$count..1 | 
    ForEach-Object{
        if($wb.Worksheets[1].UsedRange.Cells.Item($_,10).Text.Length -eq 0){
            $wb.Worksheets[1].UsedRange.Cells.Item($_,10).EntireRow.Delete()
        }
    }

\(ツ)_/

_{Wednesday, May 30, 2018 3:27 PM}

It is the accepted method in PowerShell with Office COM.

\(ツ)_/

_{Wednesday, May 30, 2018 5:12 PM}

Open Task Manager and kill all copies of Excel then run again.

\(ツ)_/

_{Wednesday, May 30, 2018 8:18 PM}

If you are using older versions of Excel then run the 32 bit version of PowerShell.  Be sure that all copies of Excel are purged from memory before running the test.

Any spreadsheet that has 1000s of rows and many blank rows will always be slow on a delete.

\(ツ)_/

_{Wednesday, May 30, 2018 8:33 PM | 1 vote}

This method should be somewhat faster:

$cells = $wb.Worksheets[1].UsedRange.Columns[10].SpecialCells([Microsoft.Office.Interop.Excel.XlCellType]::xlCellTypeBlanks)
$cells.Count..1 | %{$cells[$_].EntireRow.Delete()}

\(ツ)_/

_{Wednesday, May 30, 2018 9:05 PM}

Thank you! Unfortunately that seems to error out. not sure what the error is as it doesn't display anything.

Thanks!

_{Wednesday, May 30, 2018 9:13 PM}

Thank you! Unfortunately that seems to error out. not sure what the error is as it doesn't display anything.

Thanks!

What error out?  You have to be specific. Are you running a 32 bit version of PowerShell like I suggested?

\(ツ)_/

_{Wednesday, May 30, 2018 10:40 PM}

I am running the latest version of Excel. The new piece of code is erroring out.

Are you running a 32 bit version of PowerShell?

[environment]::Is64BitOperatingSystem
[environment]::Is64BitProcess

\(ツ)_/

_{Thursday, May 31, 2018 9:26 AM}

ImportExcel module from Doug Finke makes tasks like this considerably easier.

_{Thursday, May 31, 2018 2:00 PM}

Hi JRV -

I'm running 64 bit. Thanks!

Please run this code form the 32 bit version of PowerShell.  This will improve performance.

Run you test after a restart of your system to be sure you have removed all old copies of Excel from memory.

\(ツ)_/

_{Thursday, May 31, 2018 4:09 PM}

Just tried running with 32 bit - same issues.

As I posted above.  The overall system and spreadsheet will determine performance.  If the methods described are all slow then maybe your spreadsheet is too big or, perhaps, your computer is too small. (one processor low memory).

Without an exact example  of the spreadsheet there is really no way for use to know.

\(ツ)_/

_{Thursday, May 31, 2018 4:54 PM}

No I mean your second suggestion of powershell code does not work.

Thanks!

Which second code?  Please post the exact code you are using. 

I thought your issue was with speed?

\(ツ)_/

_{Thursday, May 31, 2018 11:35 PM}

This is near instantaneous:

$xl = New-Object -comobject excel.application
$xl.Visible = $False
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("d:\test\book1.xlsx")
$xl.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationManual

$cells = $wb.Worksheets[1].UsedRange.Columns[10].SpecialCells([Microsoft.Office.Interop.Excel.XlCellType]::xlCellTypeBlanks)
$cells.EntireRow.Select()
$xl.Selection.Delete(1)

\(ツ)_/

_{Friday, June 1, 2018 1:01 AM}

Post a link to a copy of your Excel file.   I don't feel like working hard to turn text into Excel since the text has no delimiters.

Use you favorite file sharing site.  OneDrive is a good choice.

\(ツ)_/

_{Friday, June 1, 2018 8:13 AM}

The issue is that your file is corrupt.  Whatever damaged it I cannot find any way to fix it.  This would explain the slowness as the cells cannot be correctly selected.

Even so the following method takes less than 4 seconds on my system:

Measure-Command {
    $count = $wb.Worksheets[1].UsedRange.Columns[10].Cells.Count
    $count..1 | 
    ForEach-Object{
        if($wb.Worksheets[1].UsedRange.Cells.Item($_,10).Text.Length -eq 0){
            [void]$wb.Worksheets[1].UsedRange.Cells.Item($_,10).EntireRow.Delete()
        }
    }
}

I suspect your Office install may be damaged or you have other system problems.

\(ツ)_/

_{Friday, June 1, 2018 10:24 AM}

When I try to select any column it selects the first row. This happens when there is corruption in the file. It can be caused by unprintable characters. THe cell-by-cell method does not use "select()" so it isn't prone to this error.

Also if I try to display the file in any formatted view there are two dummy columns in the middle with no header. They are the just before column "J". 

\(ツ)_/

_{Friday, June 1, 2018 10:37 AM}

Interesting - thank you for the heads up.

Also, if I save the file as csv and then run your code, it works fine.

_{Friday, June 1, 2018 11:18 AM}

Interesting - thank you for the heads up.

Also, if I save the file as csv and then run your code, it works fine.

More proof that the original file is corrupt.  The CSV strips out all internal items and just saves the cell contents.  RE-importing gives you a new sheet with no formatting or column specs as all are regenerated.

The header cells may have been merged and have broken the file.  This may indicate that you are missing updates or patches to Office or Windows.

\(ツ)_/