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

[Marcin]

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.

 

[Chip Pearson]

Try something like

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


Change both occurances of "A" to the appropriate column.

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

 

[Dave]

there is not a single function to do this. you will have to set up a range
of cells to search, iterate through the cells searching for the string you
want, then select the row and delete it if you find what you are looking
for. just one warning, when you delete the row the rows below it will move
up, so the cell you are looking at will get replaced by the one below it...
if you aren't careful you can end up missing the row after each one you
delete. one way around this is to search from the bottom up.

 

[Marcin]

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

 

[Tony Jollans]

I would probably use an AutoFilter for this. You might have to be a bit
careful with the first row in the selection but otherwise this should do it
without having to loop and worry about the effect of deletions on the loop
....

Selection.AutoFilter Field:=1, Criteria1:="=*NOT*"
Selection.EntireRow.Delete Shift:=xlUp