What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

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.

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

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.

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

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

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

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

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

How something like this. I want delete rows which don't include a word
"not".
Delete Rows without "not" by using this AutoFilter function.
Because in a normal way it takes a long time to search 2000 rows.
Thanks for response

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

Use this then

DeleteValue = "<*not*"



--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
How something like this. I want delete rows which don't include a word
"not".
Delete Rows without "not" by using this AutoFilter function.
Because in a normal way it takes a long time to search 2000 rows.
Thanks for response

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

Thanks for answer

What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
 

Hello,
Do You know fast method of connecting to cells to each other.
I want to search kolumns (2000rows) when find a word for example not I
want to write to another kolumn in the same row for example "Here is
not word"
Like:
________A________B__________
1 | This is a L2 | level 2
2 | This is L4 | level 4
3 | This is not | word "not"
4 | This is L5 | level 5
...........................
40| This isn't L6 | level 6
41| etc.. |
I know method of doing something like this
For Each oCell In Selection
"here is a function findind a word and pasting in B cell"
Next oCell
But how do it in a quickier way.For example using your code?
Thanks for response