Macro to delete rows based on cell color

[ultracyclist]

I created the following macro to delete all rows that have a cell background color of Yellow in Column D

Sub deleterow()
     Dim myRange as Range
     Dim cell As Range

     Application.Calculation = xlCalculationManual
     Set myRange = Worksheets(1).Range("D2:D1200") 
  For Each cell In myRange
        If cell.Interior.ColorIndex = 6 Then
          cell.EntireRow.Delete
        End If
     Next cell

     Application.Calculation = xlCalculationAutomatic
End Sub

</SPAN>


When I run the code some rows that are in Yellow do not get deleted.

Any thoughts?

Thanks,

Allen</SPAN>

 

[JoeMo]

You need to delete from bottom to top. Your code is using a For Each - Next loop which proceeds from top to bottom. Try:

For i = myRange.rows.count  to 1 Step -1
If myRange(i).Interior.ColorIndex = 6 then
myRange(i).EntireRow.Delete
End If
Next i

 

[AlphaFrog]

Deleted

 

[ultracyclist]

Should I replace the following code

For Each cell In myRange
        If cell.Interior.ColorIndex = 6 Then
          cell.EntireRow.Delete
        End If
     Next cell

with

For i = myRange.rows.count  to 1 Step -1
If myRange(i).Interior.ColorIndex = 6 then
myRange(i).EntireRow.Delete
End If
Next I

 

[JoeMo]

That's the idea. But Next I should be Next i as in my post.

 

[ultracyclist]

Thank you for the clarification. I tested the code and everything works as expected.

Thank you again for your help.

Regards,

Allen

 

[JoeMo]

Thank you for the clarification. I tested the code and everything works as expected.

Thank you again for your help.

Regards,

Allen

You are welcome - thanks for the reply.