For each Next Loop
 

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

For each Next Loop
 

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


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

For each Next Loop
 

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

For each Next Loop
 

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
--
Gary''s Student


"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

For each Next Loop
 

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.

--
Regards,
Tom Ogilvy

"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

For each Next Loop
 

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

--
Regards,
Tom Ogilvy


"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

For each Next Loop
 

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

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



"Jim Thomlinson" wrote:

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

For each Next Loop
 

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"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