Hello,
I have a problem with finding a string in a selection range and than
deletes row including this string. For example:
I have in column A:
________A________
1 | This is a cat |
2 | This is not a cat |
3 | This is not a dog |
4 | This is a fly |
...........................
40| This is not a horse |
41| etc.. |
I want to find cells which includs string "not" and delete a row with
this string.
How can I do this in a simply way?
Thanks for response.

You could use the VBA InStr() function

This code looks for the last data in column A and then deletes any rows
where there is " not " (Note the spaces) in any cell. Look in help for the
return values of the InStr() function

Sub findword()
Dim lLastRow As Long
Dim x As Long
lLastRow = Range("A65536").End(xlUp).Row
For x = lLastRow To 1 Step -1
If InStr(1, Range("A" & x).Value, " not ", 1) < 0 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


wrote in message
oups.com...
Hello,
I have a problem with finding a string in a selection range and than
deletes row including this string. For example:
I have in column A:
________A________
1 | This is a cat |
2 | This is not a cat |
3 | This is not a dog |
4 | This is a fly |
..........................
40| This is not a horse |
41| etc.. |
I want to find cells which includs string "not" and delete a row with
this string.
How can I do this in a simply way?
Thanks for response.

Thank for answer.
How to do this for also for lagre letters? This recognizes lagre and
small letters?
How to do this in general?
Thanks for reply

Try

Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If LCase(Cells(RowNdx, "A").Text) Like "* not *" Then
Rows(RowNdx).Delete
End If
Next RowNdx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



wrote in message
oups.com...
Thank for answer.
How to do this for also for lagre letters? This recognizes
lagre and
small letters?
How to do this in general?
Thanks for reply