simple loop through cells does not work as I want it to

[lakersbg]

Trying to do a simple loop through cells in column J (which contain "yes" or "no" values) and delete the rows which of the "yes" cells. However, not all "yes" cells are deleted... Thank you in advance for the help
The code is the following:

Dim myrange As Range
maturedRangeCell = Range("J5").End(xlDown).Address(RowAbsolute:=False)
Set myrange = Range("J5", maturedRangeCell)
For Each cell In myrange.Cells
If cell = "Yes" Then cell.EntireRow.Delete

Next cell

 

[njimack]

When deleting rows, you always need to work backwards...

Sub x()
Dim i As Long
Dim lastrow As Long
Application.ScreenUpdating = False
lastrow = Range("J5").End(xlDown).Row
For i = lastrow To 5 Step -1
    If Cells(i, 10) = "Yes" Then Cells(i, 10).EntireRow.Delete
Next i
Application.ScreenUpdating = True
 
End Sub

 

[VoG]

You need to loop backwards

Sub test()
Dim LR As Range, i As Range
LR = Range("J" & Rows.Count).End(xlup).Row
For i = LR To 5 Step -1
    If Range("J" & i).Value = "Yes" Then Rows(i).Delete
Next i
End Sub

 

[lakersbg]

Why do I need to loop backwards?
The code you suggested does not run, I get type mismatch and missing object errors.=>it runs if I delete the variables definitions (could not assign row num to range)
Thank you in advance for the help

 

[Jonmo1]

You have to go backwards because....

when you're going normally, from 2 to 3 to 4 etc..
Say you get to row 3 and it meets the criteria and the row is deleted.
Then the data that WAS in row 4 is now in Row 3.
But the loop then moves on to test row 4 (which now contains data that WAS in row 5)
The value originally in Row 4 got skipped.

Going backwards (10 to 9 to 8 etc) avoids that problem.


Vog's probably didn't work because i should be dimmed as Long, not Range..
njimack's should work fine.

 

[njimack]

Why do I need to loop backwards?
The code you suggested does not run, I get type mismatch and missing object errors.
Thank you in advance for the help

Who's code did you use? Which line gave the error?

If you don't loop backwards, then after you've deleted the first row, all the other rows shift up by one row. When the code advances to the next cell, it doesn't take account of the fact that everything has shifted up by one row, and so you end up only testing every other row.

 

[VoG]

Apologies

Sub test()
Dim LR As Long, i As Long
LR = Range("J" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
    If Range("J" & i).Value = "Yes" Then Rows(i).Delete
Next i
End Sub

If you loop forwards you may miss some row2s, This is well documented.

 

[lakersbg]

Thank you guys for the replies! The code works fine. ray:
I realized from the beginning that VBA is not evaluating the proper row but couldn't explain why..

 

[Jonmo1]

You can avoid the issue alltogether by not using a loop in the first place.

Try using the Autofilter.

Sub Macro1()
Dim lr As Long
lr = Range("J5").End(xlDown).Row
With Range("J4:J" & lr)
    .AutoFilter Field:=1, Criteria1:="Yes"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[Rick Rothstein]

Why do I need to loop backwards?

Consider this example... let's say Rows 4 and 5 have "yes" in them and your loop is on Row 4 at the moment... your code deletes that row and all the rows below it move up one so that the "yes" that was in Row 5 is now in Row 4... but your loop has already processed Row 4 and it will not process it again (loops do not work that way) so on the next iteration of the loop, the row counter moves from 4 to 5 and the "yes" that is now in Row 4 gets skipped over. When you run the loop backwards (from the bottom up), that can't happen (there will be no "yes" values in the cells that move up as they were removed by prior loops).