Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
How do I create a For loop within a For loop? | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |