Finding multiple words in excel and deleting row using vba

Question

I tried creating my own script based on two scripts I found on stack but I can't make it seem to work. So what I'm trying to do is find certain words in my excel document and then delete the row that the data is on.

The pattern of the strings that I am looking for is eventually going to grow in time so I need to be able to update my array and have my vba script delete any row that matches my pattern.

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim MyVar

For Each WS In ThisWorkbook.Worksheets
    With WS
        pattern = "string 1/ string 2/ string 3"
        MyVar = Split(pattern, "/")

        RowCount = ActiveSheet.UsedRange.Rows.Count

        Dim i As Integer
        For i = 2 To RowCount
            Dim j As Integer
            For j = 1 To 3 'find the word within this range
                If Cells(i, j) = pattern Then
                    Cells(i, j).EntireRow.Delete
                End If
            Next j
        Next i
    End With
Next WS

End Sub

Shai Rado · Accepted Answer · 2018-03-24 05:13:16Z

2

First, you have With WS but all your objects inside it are not referenced with that With statement, since your are missing the ..

So RowCount = ActiveSheet.UsedRange.Rows.Count should be RowCount = .UsedRange.Rows.Count. Also If Cells(i, j)... should be If .Cells(i, j)...

Second, a good way to check if a string in a certain cell is found within an array, in your case MyVar, which contains all your pattern, use the Match function:

If Not IsError(Application.Match(.Cells(i, j).Value, MyVar, 0)) Then

More explanations inside the code below:

Code

Option Explicit

Sub Deletrows_Click()

Dim WS As Worksheet
Dim pattern As String
Dim MyVar
Dim RowCount As Long, i As Long, j As Long
Dim DelRng As Range

' take it outside the loop, no need to re-create the array every time inside the loop
pattern = "string 1/ string 2/ string 3"
MyVar = Split(pattern, "/")

For Each WS In ThisWorkbook.Worksheets
    With WS
        RowCount = .UsedRange.Rows.Count

        For i = 2 To RowCount
            For j = 1 To 3 'find the word within this range

                ' you can use Match to see if cell's value is found within an array
                If Not IsError(Application.Match(.Cells(i, j).Value, MyVar, 0)) Then
                    If Not DelRng Is Nothing Then
                        Set DelRng = Application.Union(DelRng, .Cells(i, j))
                    Else
                        Set DelRng = .Cells(i, j)
                    End If
                End If
            Next j
        Next i
    End With

    ' after looping through all cells, delete all rows with words in pattern at onc shot
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete shift:=xlShiftUp

    Set DelRng = Nothing ' reset range object
Next WS

End Sub

answered Mar 24, 2018 at 5:13

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Glenn Over a year ago

Shai Rado Thank you sir that helped me alot, your the best, I also like your comments it was very helpful understanding the logic

Glenn Over a year ago

How do I modify this to look for certain words in a different column?

Shai Rado Over a year ago

@Glenn instead MyVar use a Range object that you set up to that column

Glenn Over a year ago

Thank you again sir, that makes sense too

Collectives™ on Stack Overflow

Finding multiple words in excel and deleting row using vba

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related