How to speed up delete row script?

Question

I have a spreadsheet with 200,000+ rows. I need to go through it and if in a certain column, a cell is blank, then delete that row.

I was wondering if there's any quicker way or any ideas as to how to speed this up.

Here's what I have for the loop where it deletes rows:

For i = Cells(Rows.Count, LastRowCounter).End(xlUp).row To headerQ Step -1
    If IsEmpty(Cells(i, Column2DeleteRowsFrom).Value) Then Cells(i,Column2DeleteRowsFrom).EntireRow.Delete
Next i

Note: "lastRowCounter" is the column I chose (i.e. "A","B", etc.) "HeaderQ" is either 1 or 2, depending if I have headers.

AFAIK the main other way would be to use, instead of the for loop I have, to do something like (pseudo code)

For each cel in Range([the range]) If isempty(cel) then delete next cel

But don't know that that'd be any faster.

Thanks for any ideas/tips!

(NOTE: I have turned off screen refreshing, and also have no calculations in the sheet, it's simply data).

Why not filter on that column for blanks and delete all visible? — Raystafarian, Jun 23 '15 at 19:11
@Raystafarian - ...I have no idea why not! I didn't even think of that (been doing a lot in VBA recently) - that works just as well, thanks so much! (Meta question: can I mark that as an answer somehow, or just note that int he subject?) — BruceWayne, Jun 23 '15 at 19:16
Also FYI, a common problem with your technique above is that rows get skipped. For example, let's say you had rows 32 and 33 blank, you deleted row 32, but now row 33 is row 32 and the counter is still on row 33 so that gets skipped. You could either skip incrementing the counter when you delete, or my preferred method, just add all blank cells to a range with `union` and then `Range1.EntireRow.Delete` (Range1 being an example Range name). This would help when you're trying to delete on some criteria that you can't use SpecialCells for. — puzzlepiece87, Jun 23 '15 at 20:54
@puzzlepiece87 the code is iterating from the last row backwards, this prevents the issue of skipping rows. — SierraOscar, Jun 23 '15 at 21:29
[ONE WAY](http://stackoverflow.com/questions/20077945/delete-cells-in-an-excel-column-when-rows-0) Do not delete them in the loop. Store them in a range and then delete them outside the loop. [ANOTHER WAY](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) Use of Autofilter. Filter on Blanks and then delete them in one go. — Siddharth Rout, Jun 23 '15 at 21:38

SierraOscar · Answer 1 · 2015-06-24T07:53:28.833

3

Or use SpecialCells if you're using Excel 2010 or later...

Range(Cells(headerQ, Column2DeleteRowsFrom), Cells(Rows.Count, Cells(Rows.Count, LastRowCounter).End(xlUp).Row)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

edited Jun 24 '15 at 07:53

answered Jun 23 '15 at 19:16

SierraOscar

17,507
6
40
68

Note that this code will determine the last row from the contents of the column containing blanks - which is not desired or practical according to the OP's request. – user1016274 Jun 24 '15 at 07:46
Last row is determined using column `LastRowCounter`, empty cells are checked in column `Column2DeleteRowsFrom`. BTW, the `SpecialCells` bug is fixed in XL 2010 and newer, but still present in XL 2007. – user1016274 Jun 24 '15 at 07:52
Well spotted - the logic is there anyway, the point being that SpecialCells is faster than looping. – SierraOscar Jun 24 '15 at 07:55

score 3 · Accepted Answer · answered Jun 23 '15 at 19:32

3

Use the SpecialCells method to select all relevant cells at once and delete the entire row of each:

Sub delemtpy()
    Dim testcol As Range
    Dim lastRow As Long
    Dim headerQ As Long
    Dim Column2DeleteRowsFrom As Long
    Dim LastRowCounter As Long

    lastRow = Cells(Rows.Count, LastRowCounter).End(xlUp).Row
    Set testcol = Range(Cells(headerQ, Column2DeleteRowsFrom), Cells(lastRow, Column2DeleteRowsFrom))
    On Error Resume Next  ' if no empty cells present
    testcol.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
End Sub

This will handle the corner case where the search column contains no empty cells at all. Note the use of LastRowCounter to determine the used range.

answered Jun 23 '15 at 19:32

user1016274

4,071
1
23
19

It's important to point out that in older versions of Excel, under certain conditions, using SpecialCells in this way can actually wipe out all your data - it's best to test for how many areas there are rather than using `On Error Resume Next` but for the majority of users this isn't a problem. http://www.rondebruin.nl/win/s4/win003.htm – SierraOscar Jun 23 '15 at 21:34
user1016274 and @S-O, thanks! The SpecialCells really speeds things up, I'll be using that! – BruceWayne Jun 24 '15 at 16:25

How to speed up delete row script?

2 Answers2