For each Next Loop

[Guest]

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

 

[Chip Pearson]

Try the following:

Sub deleteUSD()
Dim RowNdx As Long

For RowNdx = 500 To 8 Step -1
If Cells(RowNdx, "E").Value = "USD" Then
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

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...

 

[Guest]

Try:

Sub Macrox()
Dim i As Integer
For i = 500 To 8 Step -1
If Cells(i, 5).Value = "USD" Then
Rows(i).EntireRow.Delete
End If
Next
End Sub

The important thing is to go backwards

 

[Guest]

by the way. the reason you are getting the error is because

myrange = Range("E8:E500")

should be

Dim myRange as Range

set myRange = range("E8:E500")

You haven't yet gotten to the problems described by the others, so you need
to change your approach as well. This was just for information - not a
suggestion that that change will give the final result you desire.

 

[Guest]

Here is one more
Sub DeleteUSD()
Dim rng as Range
set rng = columns(5).Find("USD")
if not rng is nothing then
do
rng.EntireRow.Delete
set rng = columns(5).Find("USD")
loop while not rng is nothing
End if
end Sub

 

[Guest]

it is not a big difference in this case...

Especially since you are only deleting one range object, not several
hundred. Correct?

 

[Guest]

Thanks everyone for your responses. There are certainly many ways to do the
same thing. I've learn the short cut this time.