delete rows with criteria
 

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

delete rows with criteria
 

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



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


"S.E." wrote in message ...
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

delete rows with criteria
 

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

"Ron de Bruin" wrote in message
...
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



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


"S.E." wrote in message
...
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

delete rows with criteria
 

Oops

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


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


"S.E." wrote in message ...
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

"Ron de Bruin" wrote in message ...
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



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


"S.E." wrote in message ...
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

delete rows with criteria
 

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

"Ron de Bruin" wrote in message
...
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



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


"S.E." wrote in message
...
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

delete rows with criteria
 

Nevermind. Now it works. Thanks Ron.

Scott

"S.E." wrote in message
...
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

"Ron de Bruin" wrote in message
...
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



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


"S.E." wrote in message
...
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