A better way in VBA to remove a whole row if one cell contains one certain word?

Question

I wrote the following code, which looks for 3 words in the column G and then in case, that one of those occurs it delete the whole row. However, it is not so efficient(quick). I guess because of 3 If and ElseIf. Does someone know a better way to do it?

 Last = Workbooks("reportI.xlsm").Sheets("SII_I").Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To Last Step 1
    If (Workbooks("reportI.xlsm").Sheets("SII_I").Cells(i, "G").Value) = "01NU SfG" Then
          Workbooks("reportI.xlsm").Sheets("SII_I").Cells(i, "A").EntireRow.Delete
    '
    'with the word "01NU" in column  G
     ElseIf (Workbooks("reportI.xlsm").Sheets("SII_I").Cells(i, "G").Value) = "01NU" Then
          Workbooks("reportI.xlsm").Sheets("SII_I").Cells(i, "A").EntireRow.Delete
     'with the word "11G SfG" in column  G
     ElseIf (Workbooks("reportI.xlsm").Sheets("SII_I").Cells(i, "G").Value) = "11G SfG" Then
          Cells(i, "A").EntireRow.Delete
    End If
    Debug.Print i
Next i

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

You can use just one if clause by using the OR operator.

If "A1"= "01NU OR "A1" = "SfG" OR "A1" = "11G SfG" Then
'delete row

Alternatively, you can get your macro to filter that column for the values 01NU, SfG, 11G SfG, and then delete all the filtered rows. This is definitely more faster.

Just replace range A1 by your required range.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 28 '17 at 07:45

ProgSnob

483
1
9
20

score 1 · Answer 2 · answered Jun 28 '17 at 08:40

Another solution:

Sub Demo()
    Dim delItems As String
    Dim rng As Range, searchRng As Range, cel As Range
    Dim lastRow As Long

    delItems = "01NU SfG,01NU,11G SfG" 'search items
    With Workbooks("reportI.xlsm").Sheets("SII_I")
        lastRow = .Cells(Rows.Count, "G").End(xlUp).Row
        Set searchRng = .Range("G1:G" & lastRow)

        For Each cel In searchRng
            If InStr(1, delItems, cel.Value, vbTextCompare) Then
                If rng Is Nothing Then
                    Set rng = .Rows(cel.Row)
                Else
                    Set rng = Union(rng, .Rows(cel.Row))
                End If
            End If
        Next cel
    End With
    rng.Delete
End Sub

Vegard · Accepted Answer · 2017-06-28T08:15:31.760

The code would need a little alteration to fit your needs, but this answer is very robust and scalable.

For example:

Sub Sample()
    Dim DeleteThese(3) As String, strg As String
    Dim rng As Range
    Dim Delim As String
    Dim Last As Long
    Dim ws As Worksheet

    Set ws = Workbooks("reportI.xlsm").Sheets("SII_I")

    Last = ws.Cells(Rows.Count, "G").End(xlUp).Row

    Delim = "#"

    DeleteThese(0) = "01NU SfG"
    DeleteThese(1) = "01NU"
    DeleteThese(2) = "11G SfG"

    strg = Join(DeleteThese, Delim)
    strg = Delim & strg

    For i = 2 To Last Step 1
        If InStr(1, strg, Delim & ws.Range("G" & i).Value & Delim, vbTextCompare) Then _
        ws.Range("G" & i).EntireRow.Delete
    Next i
End Sub

I get an error with 'strg = Join(Mainfram, Delim)' run time error 13 ?! You mean "DeleteThese" — maniA, Jun 28 '17 at 08:07

A better way in VBA to remove a whole row if one cell contains one certain word?

3 Answers3