none
Need a quick way to delete a large amount of empty rows in xlsx file RRS feed

  • Question

  • Hi everyone, I need a function that will allow me to quickly clean up the empty rows in an xlsx. I currently have a function that does the job, but does so incredibly slowly considering there are over 1Mil empty rows, compared to about 8k rows of actual data. Any help with this issue would be greatly appreciated. I've included the current bit of code for reference.

    #Counts rows in
    $sheetRange = $sheet.UsedRange
    $sheetrowCount = $sheetRange.Rows.Count

    #Cleanup Empty Rows
    $max = $sheet.UsedRange.Rows.Count
    for ($i = $max; $i -ge 6; $i--) {
        if ($sheet.Cells.Item($i, 1).Text -eq '') {
            $heetRange = $sheet.Rows.Item($i).Delete()
        }
    }


    • Edited by Nega11 Monday, May 20, 2019 2:24 PM
    Monday, May 20, 2019 2:23 PM

Answers

  • The fastest way is to select the range and delete the range.  This will prevent the per-row round trip you are making which will slow things down.

    If thee rows are not in a single range then using a macro will be much faster as it will alos be run in the Excel session and not as a set of round trips.


    \_(ツ)_/


    • Edited by jrvModerator Monday, May 20, 2019 5:33 PM
    • Marked as answer by Nega11 Monday, May 20, 2019 6:18 PM
    Monday, May 20, 2019 5:31 PM
    Moderator

All replies

  • "quick" and "com" aren't two words that should be used in the same sentence.

    You might want to check if you can delete rows other ways, for example:

    https://github.com/dfinke/ImportExcel

    Monday, May 20, 2019 4:04 PM
  • "quick" and "com" aren't two words that should be used in the same sentence.

    Fair enough, but I would imagine that there would be atleast a faster way to delete these rows rather than iterating through a million rows, deleting one by one. Perhaps, is there a way to delete a range of rows in one batch, given I that know what row to begin deletion and what row to end deleting?

    I'll make sure to read through your github link though.
    • Edited by Nega11 Monday, May 20, 2019 4:54 PM
    Monday, May 20, 2019 4:21 PM
  • The fastest way is to select the range and delete the range.  This will prevent the per-row round trip you are making which will slow things down.

    If thee rows are not in a single range then using a macro will be much faster as it will alos be run in the Excel session and not as a set of round trips.


    \_(ツ)_/


    • Edited by jrvModerator Monday, May 20, 2019 5:33 PM
    • Marked as answer by Nega11 Monday, May 20, 2019 6:18 PM
    Monday, May 20, 2019 5:31 PM
    Moderator
  • The fastest way is to select the range and delete the range. 

    Would you be able to provide me an example of how to do this? I've been looking for exactly this online, but couldn't find it.
    Monday, May 20, 2019 6:06 PM
  • Look up the "Find" method.  It has a setting that specifies blank rows.  Place this in a macro and call the macro from Excel.


    \_(ツ)_/

    Monday, May 20, 2019 6:11 PM
    Moderator
  • Got it working, thank you
    Monday, May 20, 2019 6:19 PM