delete rows with criteria

S

S.E.

I want to delete all rows that do not have the word "gift" in column A. The
cells in column A often have "gift" along with some other words. It must be
simple to write a wildcard that includes "gift", but I don't know how yet.
Thanks for your help.

Scott
 
R

Ron de Bruin

Try this

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

You can also Filter (faster)
Look here
http://www.rondebruin.nl/delete.htm
 
S

S.E.

That's just about what I need. The only problem is that I want to delete the
row if it does NOT contain "gift". This macro deletes the ones that DO
contain "gift".

Scott
 
R

Ron de Bruin

Oops

ElseIf Not .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete
 
S

S.E.

Another thing. This seems to just clear the rows. In the macro I would like
to move the other rows up to take the place of the deleted rows.

Scott
 
S

S.E.

Nevermind. Now it works. Thanks Ron.

Scott

S.E. said:
Another thing. This seems to just clear the rows. In the macro I would
like to move the other rows up to take the place of the deleted rows.

Scott
Click to expand...
 
Top