Delete rows (dynamic amount) based on cell's content

Question

Sorry if this is simple, this is my first time trying VBA.

So I want this macro to get rid of rows I don't need, and for every entity it has a total field (about every 20 records or so) and I made this script:

Dim i As Integer
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row

For i = 3 To LastRow
    If Range("C" & i) = "Result" Then
        Rows(i & ":" & i).Select
        Selection.Delete Shift:=x1Up
    End If
Next

And that worked perfectly! Then I tried to a similar thing.. I tried to go through each row (record) in a data set and then if a certain field does not contain the string "INVOICE" then I don't need that row and I can delete it. So I just added into my current loop (why loop twice?) So now it looks like this:

Dim i As Integer
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row

For i = 3 To LastRow
    If Range("C" & i) = "Result" Then
        Rows(i & ":" & i).Select
        Selection.Delete Shift:=x1Up
    End If
    If Not InStr(1, Range("Q" & i), "INVOICE") Then
        Rows(i & ":" & i).Select
        Selection.Delete Shift:=x1Up
    End If
Next

That second bit as far as I can tell just randomly starts deleting rows with no rhyme or reason. Rows where the Q field doesn't contain invoice sometimes stay sometimes go, and same if it does contain invoice. Any idea what I'm doing wrong?

Don't loop. Use Autofilter :) See [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Dec 19 '13 at 23:50
...or if you're going to loop, then start on the last row and work up (`For i = LastRow to 3 Step -1`) so you won't tread on your loop index when you delete a row (thus shifting up the row below...) — Tim Williams, Dec 20 '13 at 00:41

score 1 · Answer 1 · answered Dec 20 '13 at 01:56

You should OR your conditions together so that if either reason exists the line is deleted. Otherwise since you're deleting lines within a preset range, you'll end up skipping more lines than you are currently. Currently it looks like you skip a line everytime you delete one, so you're missing any consecutive cases. Tim's advice to work from the last row up is spot on.

For i = LastRow to 3 Step -1
  If Range("C" & i) = "Result" OR Not InStr(1, Range("Q" & i), "INVOICE") Then
    Rows(i & ":" i).Delete Shift:=x1Up
  End If
Next i

That makes sense, thanks for the help! I think I'm going to switch to the autofilter option since it is faster and my datasets are quite bulky. — Birdd, Dec 20 '13 at 14:17

score 0 · Accepted Answer · answered Dec 20 '13 at 03:44

There are indeed two approaches: AutoFilter and For Loop. Of the two, AutoFilter is much faster especially with large datasets, but it will often need a very good set-up. The For Loop is easy, but it has marginal returns, especially when your data start hitting 100k rows or more.

Also, Not InStr(1, Range("Q" & i), "INVOICE") might seem like the best way but IMHO it's not. InStr returns a number, so it's better if you either do further comparison like Not InStr(1, Range("Q" & i), "INVOICE") > 0 or just simply InStr(1, Range("Q" & i), "INVOICE") = 0. In any case, I used the former in my second code below.

Following are both approaches. They are tested on simple data. The code might seem a bit bulky, but the logic is sound. Refer to the comments as well for other things.

AutoFilter approach:

Sub RemoveViaFilter()

    Dim WS As Worksheet: Set WS = ThisWorkbook.Sheets("ModifyMe")
    Dim LastRow As Long

    Application.ScreenUpdating = False
    With WS
        '--For condition "Result"
        .AutoFilterMode = False
        LastRow = .Cells(Rows.Count, 1).End(xlUp).row '--Compatible if there are more rows.
        With Range("A2:Q" & LastRow) '--Assuming your header is in Row 2 and records start at Row 3.
            .AutoFilter Field:=3, Criteria1:="Result" '--Field:=3 is Column C if data starts at A
            .Cells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete '--Delete the visible ones.
        End With
        '--For condition "<>*INVOICE*"
        .AutoFilterMode = False
        LastRow = .Cells(Rows.Count, 1).End(xlUp).row
        With Range("A2:Q" & LastRow)
            .AutoFilter Field:=17, Criteria1:="<>*INVOICE*" '--Field:=17 is Column Q if data starts at A
            .Cells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True

End Sub

For-loop approach:

Sub RemoveViaLoop()

    Dim WS As Worksheet: Set WS = ThisWorkbook.Sheets("Sheet6")
    Dim LastRow As Long: LastRow = WS.Cells(Rows.Count, 1).End(xlUp).row
    Dim Iter As Long

    Application.ScreenUpdating = False
    With WS
        For Iter = LastRow To 3 Step -1 '--Move through the rows from bottom to up by 1 step (row) at a time.
            If .Range("C" & Iter) = "Result" Or Not InStr(1, .Range("Q" & Iter).Value, "Invoice") > 0 Then
                .Rows(Iter).EntireRow.Delete
            End If
        Next Iter
    End With
    Application.ScreenUpdating = True

End Sub

Let us know if this helps.

Thank you, I'm going to try this out today using auto filter, since my datasets can get bulky and this is running a back machine that I don't want to bog down. — Birdd, Dec 20 '13 at 14:18

Delete rows (dynamic amount) based on cell's content

2 Answers2