The issue you are running into has to do with the delete. The code is moving
through each cell in the range, but as the code deletes rows the range keeps
on changing. In this instance (if you want to use a for each) you need to be
a little bit sneeky. Create a second range object to accumulate the instances
of USD...

Sub deleteUSD()
Dim rng As Range
dim myRange as range
dim rngAll as range

set myrange = Range("E8:E500")

For Each rng In myrange
If rng.Value = "USD" Then
if rngall is nothing then
set rngall = rng
else
set rngall = union(rng, rngall)
end if
End If
Next rng

if not (rngall is nothing) then rngall.entirerow.delete
End Sub

This can also be done using Find and Findnext which is a bit more efficient
but since you are only looking at a few hundred cells it is not a big
difference in this case...
--
HTH...

Jim Thomlinson


"Vanna" wrote:

Hi,
this is my first time trying out the ...for..each..next loop
I'm trying to delete the entire row if the cell in the E column contains the
word "USD". I tried the codes below and get "run time error 424, Object
require" error. Any help would be very much appreciated.

Sub deleteUSD()
Dim rng As Range

myrange = Range("E8:E500")

For Each rng In myrange
If rng.Value = "USD" Then
rng.EntireRow.Delete
End If
Next rng

End Sub