Why does this happen same code different results

[carlos_ray86]

The following code takes a range that consists of cells that are all
from the B column. When the data in the A column is red it makes the
row next to it red(the B column) along with the three rows under.
Example Column A row 20 is red so column B row 20 through 24 would
turn red. It works great everytime. However, I want those colums that
are red to be deleted so I did this little state "If Cells
(i,w).Font.ColorIndex=3 Then Cells(i,w).delete" I have i= 1 to 100 and
w=1 to 100. I thought that that would look in my range and find all of
the data in red and delete it but it doesn't for some reason just
some of the read is still there. So I thought well what if instead of
initially turning those four rows red (the statements below) I just
say .delete instead of rng3.Font.ColorIndex = 3 and the same rows that
were not delete from the prior attempt again are there. So basically I
right the same code and just switch rng3.Font.ColorIndex = 3
to .Delete but I get different outcome. Why is that? Thanks for the
help.



Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim e As Integer


For e = 1 To 300
Set rng1 = Range(Cells(e, 2), Cells(e + 1, 2))
Set rng2 = Range(Cells(e + 2, 2), Cells(e + 3, 2))
Set rng3 = Union(rng1, rng2)
If Cells(e, 1).Font.ColorIndex = 3 Then rng3.Font.ColorIndex =
3<----'Try to switch to.Delete

Next
End Sub

 

[Ken]

Carlos

Try running your loop from bottom to top (e=300 to 1 step -1). When
you are deleting rows, and you start at the top, you can miss some
stuff that you want to delete. For example, if row 4 is red it gets
deleted; but, then what was row 5 becomes row 4. So, your next time
through the loop, if the original row 5 should have been deleted, it
is spared because it became row 4 and you already checked, and
deleted, row 4.

Good luck.

Ken
Norfolk, Va

 

[Bernie Deitrick]

You should be using

For e = 300 To 1 Step -1

because as you delete rows, the rows beneath are moved up, and you end up skipping rows.

HTH,
Bernie
MS Excel MVP

 

[Mike H]

Try this

If Cells(e, 1).Font.ColorIndex = 3 Then
rng3.Select
Selection.Delete
End If

Mike

 

[carlos_ray86]

Carlos

Try running your loop from bottom to top (e=300 to 1 step -1). When
you are deleting rows, and you start at the top, you can miss some
stuff that you want to delete. For example, if row 4 is red it gets
deleted; but, then what was row 5 becomes row 4. So, your next time
through the loop, if the original row 5 should have been deleted, it
is spared because it became row 4 and you already checked, and
deleted, row 4.

Good luck.

Ken
Norfolk, Va






- Show quoted text -

That worked. I was thinking it might have to do with the fact that I
was deleting a row and it might throw it off but I forgot about the
Step function. Thanks so much it worked