VBA Delete Row contains specific exact word

Question

I want to delete rows from Excel if Column A contains "string1" as shown in the following table:

A1	B1
string1 string2	string3 string4
string5 string6	string7 string8

I'm using the below code:

 Sub DeleteRows()

    Dim rng As Range
    Dim pos As Integer
    Set rng = ActiveSheet.UsedRange
    
    For i = rng.Cells.Count To 1 Step -1
        pos = InStr(LCase(rng.Item(i).Value), LCase("string1"))
        If pos > 0 Then
            rng.Item(i).EntireRow.Delete
        End If
    Next i
End Sub

The problem is that if the Column A contains "string11" not "string1" it's also deleting the row. Any advice, please? Thanks

https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 Well this argues for `.Value2` but in any case the first paragraph explains a potential pitfall of `.Text`. Probably won't really matter if OP's data is all strings, but I guess I tend to avoid `.Text`. — BigBen, Aug 05 '21 at 13:32
@АлексейР The problem is that this "=" will not work if there is another word in the cell. I think we need to use some thing like "contains" — mg g, Aug 05 '21 at 13:44

Алексей Р · Answer 1 · 2021-08-05T16:25:03.430

3

With Split() (tested; not optimized):

Sub DeleteRows()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    For i = rng.Cells.Count To 1 Step -1
        For Each w In Split(LCase(rng(i).Value))
            If w = "string1" Then
                rng(i).EntireRow.Delete
                Exit For
            End If
        Next
    Next i
End Sub

Edit 2

Sub DeleteRows()
    Dim rng As Range, i As Long, w, arr
    arr = Array("string1", "string2", "string3")
    Set rng = ActiveSheet.UsedRange
    
    For i = rng.Cells.Count To 1 Step -1
        For Each w In Split(LCase(rng(i).Value))
            If IsNumeric(Application.Match(w, arr, 0)) Then
                rng(i).EntireRow.Delete
                Exit For
            End If
        Next
    Next i
End Sub

edited Aug 05 '21 at 16:25

answered Aug 05 '21 at 13:53

Алексей Р

7,507
2
7
18

Thanks a lot @АлексейР.. This is really helped.. instead of "=" can we search for in a list of strings, like: If w in ("string1, "string2", "string3")? – mg g Aug 05 '21 at 15:22
You can use Application.Match(w,array(),0) – Алексей Р Aug 05 '21 at 15:31
@ АлексейР.. Thanks for answering, but I'm a little bit new to VBA.. How to put this is the code you put above, please? – mg g Aug 05 '21 at 15:36
See Edit2 with `Application.Match()` – Алексей Р Aug 05 '21 at 16:25

ed2 · Answer 2 · 2021-08-05T13:54:12.550

1

Try combining the "found in string" with "string is same length". Like this:

Sub DeleteRows()

   Dim rng As Range
   Set rng = ActiveSheet.UsedRange
   
   For i = rng.Cells.Count To 1 Step -1
       pos = InStr(LCase(rng.Item(i).Value), LCase("delete"))
       If pos > 0 And (Len(rng.Item(i).Value) = Len(Lcase("delete"))) Then
           rng.Item(i).EntireRow.Delete
       End If
   Next i
End Sub

Or if you wanted to compare the entire cell contents you could just simplify it to:

Sub DeleteRows()

   Dim rng As Range
   Dim pos As Integer
   Set rng = ActiveSheet.UsedRange
   
   For i = rng.Cells.Count To 1 Step -1
       If LCase(rng.Item(i).Value) = LCase("delete") Then rng.Item(i).EntireRow.Delete
   Next i
End Sub

edited Aug 05 '21 at 13:54

answered Aug 05 '21 at 13:25

ed2

1,457
1
9
26

Why not simply use `=` or `StrComp` instead of `InStr`? – FunThomas Aug 05 '21 at 13:43
Because original solution (retained) was created with minimal changes to the OP's original code. Also now edited to add a simpler solution for if the OP wants to compare to entire cell contents instead of a word. – ed2 Aug 05 '21 at 13:57

score 0 · Answer 3 · edited Aug 06 '21 at 09:57

0

You don't need the pos variable, just compare the values in If statement!

Sub DeleteRows()

    Dim rng As Range
    Dim pos As Integer
    Set rng = ActiveSheet.UsedRange
    
    For i = rng.Cells.Count To 1 Step -1
        If rng.Item(i).Value = "delete" Then
            rng.Item(i).EntireRow.Delete
        End If
    Next i
End Sub

edited Aug 06 '21 at 09:57

Dominique

16,450
15
56
112

answered Aug 05 '21 at 14:18

Eduardo Menon

1
1

VBA Delete Row contains specific exact word

3 Answers3

Edit 2