How to delete rows from table, based on parameter check by formula?

Question

I want to delete entire rows of data, if the formula matches the set value.

I am running a check (example: sum of three columns = 0?) through a set of 17K records. The code takes around 20 minutes to complete.

Dim currentRow As Integer
Dim rowCheck As Long
Dim ws As Worksheet

Set ws = ActiveSheet

For currentRow = ws.UsedRange.Rows.Count To 2 Step -1
    rowCheck = Application.WorksheetFunction.Sum(Cells(currentRow, 5), Cells(currentRow, 6), Cells(currentRow, 7))

    Select Case rowCheck
        Case 0
            ws.Rows(currentRow).Delete ' it takes around 20 minutes to complete with 17K records to run through
        Case Else
    End Select
Next

Set ws = Nothing

The code is working, however, it seems, I am doing something wrong, as I believe the code should work so much faster with given set of data (only 17K records).

Is there a way to optimize the deletion line?

Out of interest, is there a reason you're using case select rather than a simple if-statement, since you're only checking for one argument? Not sure if that has any influence on the speed but it seems a bit excessive in this case. — Plutian, Sep 11 '19 at 12:26
Hello. Yes, it also seemed to me, that Select/Case is a bit too complicated for one check. The reason was to keep the code the same, if I will be scaling this to another set of data (with more checks to perform). — Ivans Nozdračevs, Sep 11 '19 at 12:44
Following up to the comments below, I shall CLEAR the row, rather than DELETE it row-by-row and then sort the entire dataset. — Ivans Nozdračevs, Sep 11 '19 at 13:40

Beek · Accepted Answer · 2019-09-11T12:40:46.630

Having to go through it line for line isn't the fast way to do this. You would be better off with a temporary helper column which calculates the sum. You can then filter the range on this column and delete all rows that match your criteria at once. So something like this. (assuming Column H is empty)

Dim currentRow As Integer
Dim rowCheck As Long
Dim ws As Worksheet
Dim lastRow as integer

Set ws = ActiveSheet

lastRow = ws.UsedRange.Rows.Count

ws.range("h2").formula = "=sum(e2:g2)"

ws.range("h2").autofill destination:= ws.range("h2:h" & lastRow)

ws.range("a1:h1").autofilter field:=8, criteria1:="0"

ws.range("a2:h" & lastRow).SpecialCells(xlCellTypeVisible).entirerow.delete
ws.autofiltermode = false

ws.range("h1:h" & lastRow).clearcontents

set ws = Nothing

Edit: You could also filter columns E, F, and G on 0 but that only works if all values are 0 or positive. Doing it the way I suggested gives you more control, because you can easily adjust the formula you put in cell H2.

Thank you for your reply. The columns contain numeric values >=0 and blanks. Though about this also, but for the sake of time saving, considered creating new set of data a liability. I'll try to see, how the code works. Thanks again. — Ivans Nozdračevs, Sep 11 '19 at 12:48

Carl Kirstein · Answer 2 · 2019-09-11T13:10:16.467

1

It will be much faster to find all the rows that you want to delete, select all the rows, and delete them in one go, instead of doing it row by row.

let's say you found you wanted to delete rows 35, 37, 39, and 40 then the code will be something like

for each row in row_to_evaluate
    delete_row = evaluate(row)
    if delete_row = True then Delete_Row_List = Delete_Row_List & "," & row
next

'Example: Delete_Row_List = "35,37,39,40"
Rows(Delete_Row_List).Delete Shift:=xlUp

also remember application.screenupdating = False before you run the code and application.screenupdating = True after you run it for a bit better performance.

hope it helps

EDIT: Ah I see an answer before mine basically recommended the same

edited Sep 11 '19 at 13:10

answered Sep 11 '19 at 12:34

Carl Kirstein

61
1
6

Thank you for your reply. I thought about it also, just could not manage to find the way to select the columns properly. maybe something in the code, that followed (for the purposes of clean question, I have cut the irrelevant lines, containing formatting etc.) – Ivans Nozdračevs Sep 11 '19 at 12:50
Omitting the use of `.select` negates the need for screenupdating to be turned off. You can do `Rows(Delete_Row_List).Delete` directly. [Using select should be done sparingly](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) as it invites user error. – Plutian Sep 11 '19 at 13:05
thank @Plutian I made the edit. You are right, just left the bad coding there in my haste – Carl Kirstein Sep 11 '19 at 13:11
No worries. It has been pointed out in my own answers a few times, so I'm just passing on the Karma so we can all learn. – Plutian Sep 11 '19 at 13:13

score 0 · Answer 3 · answered Sep 12 '19 at 05:03

It just occurred to me to speed up your code substantially. I assumed that you have to use the delete rows capability, but it will actually be much faster to do the following (note this is pseudo code, panel beat to work for you):

with thisworkbook.worksheets("Sheet1")
    redim New_Sheet(1 to nr_rows, 1 to nr_columns) as variant
    Old_Sheet = .range(.cells(1,1),.cells(nr_rows,nr_columns)
    'keep the headers
    for col = 1 to nr_columns
         New_Sheet(1,col) = Old_Sheet(1,col)
    next col
    k = 1
    for row = 2 to nr_rows 'start at 2 to protect the headers
        keep_row = evaluate_row(row,Old_sheet) 'this function must evaluate the row. return True if you want to keep the row, return false if you want to delete it
        if keep_row then 
            k = k+1
            for col = 1 to nr_col
                New_sheet(k,col) = Old_Sheet(row,col)
            next col
     next row
     .range(.cells(1,1),.cells(nr_rows,nr_columns) = New_Sheet

end with

Thank you for the update. I have tried different approaches, however the fastest one was from Mr. Beek (create checksum column > populate it > apply autofilter > filter out '0' > delete rows > delete checksum column). — Ivans Nozdračevs, Sep 12 '19 at 05:39

How to delete rows from table, based on parameter check by formula?

3 Answers3