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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? (https://www.excelbanter.com/excel-worksheet-functions/55247-what-function-vba-excel-witch-finds-string-like-not-cell-then-deletes-row-cell.html)

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?

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 EditDelete 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

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

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?

And yet another in comp.apps.spreadsheets

This question appears to have been separately posted all over.

Jerry

Nick Hodge wrote:

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