• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to delete rows containing certain keyword within text

shahin

Active Member
I have written a macro to remove rows containing certain text in it. If either of the keyword contains any text, the macro will delete the row. However, the macro doesn't work at all. Perhaps, i did something wrong in it. Hope somebody will help me rectify this. Thanks in advance.

Here is what I'm trying with:
Code:
Sub customized_row_removal()
    Dim i As Long
    i = 2
    Do Until Cells(i, 1).Value = ""
        If Cells(i, 1).Value = "mth" Or Cells(i, 1).Value = "rtd" Or Cells(i, 1).Value = "npt" Then
            Cells(i, 1).Select
            Selection.EntireRow.Delete
        End If
        i = i + 1
    Loop
End Sub


The keyword within the text I was searching in to delete:

AIRLINE DRIVE OWNER mth
A rtd REPAIRS INC
AANA MICHAEL B ET AL
ABASS OLADOKUN
ABBOTT npt P
AIRLINE AANA MTH
ABASS REPAIRS NPT


Btw, case is a great concern here. I meant, lower, upper and mix.
 
Hello Shahin
May be
Code:
Sub customized_row_removal()
    Dim i As Long
    Dim s As String
   
    i = 2
    Do Until Cells(i, 1).Value = ""
        s = Cells(i, 1).Value
        If InStr(s, "mth") > 0 Or InStr(s, "rtd") > 0 Or InStr(s, "npt") > 0 Then
            Cells(i, 1).EntireRow.Delete
        End If
        i = i + 1
    Loop
End Sub
 
Thanks YasserKhalil, for your answer. Slight optimization is needed. It works partially. However, it fully works out for the cells containing the keyword with lower case if i run the macro several times. Moreover, it can't do anything on those cells containing the same keyword with mixed case or upper case.
 
Hi ,

The macro is missing out certain entries because the deletion is being done as you work your way downwards.

Whenever you delete rows , you have to go from the bottom up.

Narayan
 
Hi ,

As an example , suppose the index is pointing to row #19 , and this row needs to be deleted.

Deleting row #19 , automatically shifts all rows below 19 up , so that the earlier row #20 is now row #19. The cursor remains where it was , on row #19.

Now , if you increment your index from 19 to 20 , you are effectively missing out the earlier row #20 , which has now become the new row #19.

Narayan
 
Hi there! The one I'm pasting below can handle all at once in a single run. However, it can only delete the text with the case noted in "instr()".
Code:
Sub customized_row_deletion()
    Dim rngDB As Range, rngU As Range, rng As Range

    For Each rng In Range("a2", Range("a" & Rows.Count))
        If InStr(rng, "mth") Or InStr(rng, "rtd") Or InStr(rng, "npt") Then
            If rngU Is Nothing Then
                Set rngU = rng
            Else
                Set rngU = Union(rngU, rng)
            End If
        End If
    Next rng
    If rngU Is Nothing Then
    Else
        rngU.EntireRow.Delete
    End If
End Sub

Btw, if the search items are "MTH","Mth","MtH" and so on. It can't find them. How can I optimize my script so that it can handle all cases?
 
Hi ,

Just convert the case of everything to lower case :
Code:
Sub customized_row_deletion()
    Dim rngDB As Range, rngU As Range, rng As Range

    For Each rng In Range("a2", Range("a" & Rows.Count))
        lcaserng = LCase(rng.Value)
        If InStr(lcaserng, "mth") Or InStr(lcaserng, "rtd") Or InStr(lcaserng, "npt") Then
            If rngU Is Nothing Then
                Set rngU = rng
            Else
                Set rngU = Union(rngU, rng)
            End If
        End If
    Next rng
    If rngU Is Nothing Then
    Else
        rngU.EntireRow.Delete
    End If
End Sub
Narayan
 
And again you did the magic, Narayan. Highly indebted to you. Thanksssssssssssssssssss a trillion.
 
Thanks sir for your answer but in that case ain't it necessary to mention the position of certain taxt I'm after? I meant, considering this example "AIRLINE DRIVE OWNER mth" mth is in 4th position.
 
First 1 is argument for where to start the search within the string. I.E. start from first character. Not what position to look for the string.

It doesn't matter where you start in this case as long as it's before where "mth" is found.

This argument becomes important only when you know that there is fixed length of characters in string that you want to ignore.
 
Back
Top