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]

I posted this in your other thread
**************************

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]

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
(e-mail address removed)

 

[mar_male]

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

 

[Chip Pearson]

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

 

[mar_male]

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

 

[Ron de Bruin]

Use this then

DeleteValue = "<>*not*"

 

[mar_male]

Thanks for answer

 

[mar_male]

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

 

[Ron de Bruin]

You can try this

Sub Mark_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.Offset(0, 1).Value = "NOT"

End With
.AutoFilterMode = False
End With
End Sub