View Single Post
  #2   Report Post  
Ron de Bruin
 
Posts: n/a
Default 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.