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

[mar_male]

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.

 

[Ron de Bruin]

Hi

You can use AutoFilter in the Data menu
Use Custom and contains

Then with a cell in the column Active press
Ctrl * to select the whole range and then Alt ; to select onlt the visible cells

Then Edit>Delete Row

With code (A1 is the header cell)

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "*not*"
' This will delete the rows with "ron" in the Range("A1:A100")
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub

 

[Nick Hodge]

Another possible answer in programming group.

There is no need to multi-post...most contributors read all the Excel
groups. This question is obviously a VBA (Programming) one so posting it as
well in WorksheetFunctions is also off topic an unnecessary

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Jerry W. Lewis]

And yet another in comp.apps.spreadsheets

This question appears to have been separately posted all over.

Jerry